The sys.dm_exec_sql_text Dynamic Management Object is the first Function that we will review. This function is used to return sql text from a batch by passing in the sql_handle. If we recall one of the DMV’s that we have previously reviewed (sys.dm_exec_requests), this DMV provided a sql_handle column for each of the requests that were executing on the server. If we take that sql_handle and pass it into the sys.dm_exec_sql_text function, we are able to extract the actual T-SQL statements that are being executed.
To illustrate how this function works, I will first need to capture sql_handle. **NOTE: I have a SQL Statement running in a loop in a separate window against the AdventureWorks2012 Database.
SELECT * FROM sys.dm_exec_requests
WHERE session_id > 50 and session_id <> @@SPID
Now that I have a sql_handle to work with, I can either call the sys.dm_exec_sql_text function directly and pass in the sql_handle, or I can run a query against my sys.dm_exec_requests DMV and use a CROSS APPLY with the sys.dm_exec_sql_text function.
SELECT * FROM sys.dm_exec_sql_text(0x0200000059598407105BDB167364C21EC3C2568FC10FF80A0000000000000000000000000000000000000000)
As we can see if the text column, the actual SQL text that I had running in a separate window is displayed here. Additionally you see columns for the Database ID and Object ID. These columns return NULL values whenever you are running ad hoc or perpared SQL Statements, which explains why they are NULL now. The ‘number’ columns refers to numbered stored procedures and the encrypted column represents whether or not the SQL text is encrypted or not.
Optionally we could have queried the sys.dm_exec_requests DMV and used a CROSS APPLY as you see below.
SELECT er.session_id, er.status, er.command, db_name(er.database_id) [Database], st.text
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE er.session_id > 50 and session_id <> @@SPID
This function is very useful when you need to extract the actual text that is being executed on a server. Many DBA’s tend to use DBCC INPUTBUFFER() but for extremely long SQL statements, the text output can be truncated when using DBCC INPUTBUFFER(). This Dynamic Management Function will never truncate your text.
For more information on this DMF, please see the below Books Online link: