(Edit: Was reading Adam’s book tonight and discovered you can pass a plan handle to this object.  Very interesting!)

This dynamic management object returns the SQL that was saved when a query was executed. This is a very exciting and useful thing to have, as there are dynamic management views that you will be able to use to get statistics for a query, as well as the full SQL for any actively executing query.

This is a tremendous leap ahead of the functionality that was available via the rather limited view available from DBCC INPUTBUFFER, where we could only get the first 256 characters. Be careful on active servers with very large queries as this can return a lot of data.

Type: Function

Parameter:

• handle (either one of the following types)
  • sql_handle – ( which can be retrieved from sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants)
  • plan_handle – ( which can be retrieved sys.dm_exec_requests, sys.dm_exec_query_memory_grants, sys.dm_exec_query_stats, sys.dm_exec_cached_plan)

Data: Snapshot, values based on current reality. Note that rows can be reset by running DBCC FREEPOCCACHE, removing all rows from the query cache.

Columns:

• Grouping: Object Reference If the query is executing an object, the following three columns will give you the pointer to the object. They will be NULL when you are executing a batch of SQL Statements.
  • dbid – surrogate key for the database, if applicable. (Note: this is usually database_id in other objects. Look for this to change in a following version)
  • objectid – surrogate key for the object in a database, if applicable. (Note: this is usually object_id in other objects. Look for this to change in a following version)
  • number – for stored procedures, can be the number for grouped procedures. Proc;1, Proc;2. Rarely used.
• encrypted – 1 if plan is encrypted, which will prevent viewing of the query text. 0 otherwise.
• text – The text of the query, unless the object is encrypted, in which case it will be NULL

Example:

See the query you are executing:

select dest.*
from  sys.dm_exec_requests as der
             cross apply sys.dm_exec_sql_text (der.sql_handle) as dest
where session_id = @@spid

This will return:

dbid   objectid    number encrypted
—— ———– —— ———
NULL   NULL        NULL   0

text
—————————————–
select dest.*
from   sys.dm_exec_requests as der
          cross apply sys.dm_exec_sql_text (der.sql_handle) as dest
where session_id = @@spid

Of course, that is kind of a silly query, but it is a repeatable result that you can execute to see how the function works. The sys.dm_exec_sql_text object will actually be of a lot more use when it is used by other dynamic management objects that have a query handle (sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants)

Note:  This is part of an ongoing project to write a book about all of the dynamic management views for Redgate. It will be freely distributable once complete as an ebook. Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.