THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

sys.dm_exec_sql_text

(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 Red-Gate. 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.  Also, I will begin maintaining the following web page once the book is closer to completion: http://drsql.org/dmvbook.aspx.

Published Wednesday, October 03, 2007 11:53 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Federico Sandi said:

Hello, i found this query really useful, im trying to develop a Trigger  for UPDATE/INSERT statements on a particular column of a table, but i need to create a log of which SQL statement triggered that trigger, maybe can u give me a little tip on this? (i found a alternative path, but is too long and complicated, i want to see if there other points of view that i can use)

April 9, 2008 1:19 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement