Currently executing SQL view

 

Useful system view that you can create to view the exact (FULL) sql statement that's currently running on a sql server. Kind of like an sp_who2 but with the full executing sql statement shown.

CREATE VIEW [Current_Running_SQL]
AS
SELECT            CASE
                        WHEN  Requests.sql_handle IS NULL
                        THEN  ' '
                        ELSE  SubString
                                    (
                                          Statements.text,
                                          (Requests.Statement_Start_Offset+2)/2,
                                          (
                                                CASE
                                                      WHEN  Requests.Statement_End_Offset = -1
                                                      THEN      LEN(CONVERT(nvarchar(MAX),Statements.text))*2
                                                      ELSE  Requests.Statement_End_Offset
                                                END
                                                -
                                                Requests.Statement_Start_Offset
                                          )
                                          /
                                          2
                                    )
                  END                                                               AS StatementText,
                  QueryPlans.query_plan                                 AS QueryPlan,
                  Statements.Text                                             AS Batch_Text,
                  Sessions.Session_ID,
                  Sessions.Login_Name,
                  Sessions.Host_Name,
                  Sessions.Program_Name,
                  Sessions.Client_Interface_Name,
                  Requests.Wait_Time,
                  Requests.Cpu_Time,
                  Requests.Total_Elapsed_Time,
                  Requests.Reads,
                  Requests.Writes,
                  Requests.Logical_Reads,
                  Requests.Row_Count,
                  Requests.Granted_Query_Memory*8/1024            AS Granted_Query_Memory_MB,
                  LEN(Statements.text)                                  AS Batch_Text_Length,
                  Requests.Statement_Start_Offset/2               AS Statement_Start_Offset,
                  CASE
                        WHEN  Requests.Statement_End_Offset = -1
                        THEN  LEN(CONVERT(nvarchar(MAX),Statements.Text))*2
                        ELSE  Requests.Statement_End_Offset
                  END    
                  /
                  2                                                                 AS Statement_End_Position,
                  (
                        CASE
                              WHEN  Requests.Statement_End_Offset = -1
                              THEN  LEN(CONVERT(nvarchar(MAX),Statements.text))*2
                              ELSE  Requests.Statement_End_Offset
                        END
                        -
                        Requests.Statement_Start_Offset
                  )
                  /
                  2                                                                 AS Statement_Text_Length
FROM        sys.dm_exec_sessions                      Sessions
INNER JOIN  sys.dm_exec_requests                      Requests
ON                Sessions.session_id                             = Requests.Session_ID
CROSS APPLY sys.dm_exec_sql_text(sql_handle)    Statements
CROSS APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans
WHERE       Sessions.Session_ID                             != @@SPID
GO