How to Get the Text of Currently Running SQL in SQL Server

debuggingdynamic-sqlsql server

This is something I am just interested in knowing if it is possible.

Could I write an SQL query that could return the text of itself as a variable?

Where I might use this would be on things like reports that I might want to return a variable that saves the actual query that ran to generate the report, or as a debugging tool.

I have seen there is:

sys.dm_exec_text_query_plan

that would return the text of the query but can't see an easy way to always get the currently executing queries ID to link back to this table.

Has anyone ever written something to do something like this?

I am using SQL Server 2008, 2012 and 2014.

Best Answer

There is no way that a query can output its own text, that I am aware of, but you can certainly capture it using sys.dm_exec_requests.

You can try this:

-- Query to Run
SELECT 100 / 10

-- Query Text Capture
DECLARE @Query NVARCHAR( MAX )
SET @Query = ( SELECT text /*, query_plan*/
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
        CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
    WHERE session_id = @@SPID );
SELECT @Query

Or inside SP:

CREATE PROCEDURE Test
AS
    SET NOCOUNT ON

    SELECT 100 / 10

    DECLARE @Query NVARCHAR( MAX )
    SET @Query = ( SELECT text /*, query_plan*/
        FROM sys.dm_exec_requests AS er
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
            CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
        WHERE session_id = @@SPID );

    SELECT @Query
GO
EXEC Test

I think you would be better served by SQL Trace though.

For a procedure, you can use sp_helptext and equivalents to get its text. I don't think you would need to capture SP text at the point execution as SPs don't get redefined often. Capturing SP name should be enough.

Related Question