{"id":82022,"date":"2007-10-03T23:53:26","date_gmt":"2007-10-03T23:53:26","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73169"},"modified":"2019-05-22T09:32:45","modified_gmt":"2019-05-22T09:32:45","slug":"sys-dm_exec_sql_text","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sys-dm_exec_sql_text\/","title":{"rendered":"sys.dm_exec_sql_text"},"content":{"rendered":"<p><em>(Edit: Was reading Adam&#8217;s book tonight and discovered you can pass a plan handle to this object.\u00a0 Very interesting!)<\/em><\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><b>Type:<\/b> Function<\/p>\n<p><b>Parameter: <\/b><\/p>\n<ul>\n<li><b>handle (either one of the following types)<\/b>\n<ul>\n<li><b>sql_handle<\/b> \u2013 ( 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)<\/li>\n<li><b>plan_handle &#8211; <\/b>( which can be retrieved sys.dm_exec_requests, sys.dm_exec_query_memory_grants, sys.dm_exec_query_stats, sys.dm_exec_cached_plan)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><b>Data:<\/b> Snapshot, values based on current reality. Note that rows can be reset by running DBCC FREEPOCCACHE, removing all rows from the query cache.<\/p>\n<p><b>Columns:<br \/>\n<\/b><\/p>\n<ul>\n<li><b><i>Grouping:<\/i><\/b><i> <b>Object Reference<\/b> <\/i>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.\n<ul>\n<li><b>dbid <\/b>&#8211; 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)<\/li>\n<li><b>objectid <\/b>&#8211; 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)<\/li>\n<li><b>number \u2013 <\/b>for stored procedures, can be the number for grouped procedures. Proc;1, Proc;2. Rarely used.<\/li>\n<\/ul>\n<\/li>\n<li><b>encrypted<\/b> \u2013 1 if plan is encrypted, which will prevent viewing of the query text. 0 otherwise.<\/li>\n<li><b>text <\/b>\u2013 The text of the query, unless the object is encrypted, in which case it will be NULL<\/li>\n<\/ul>\n<p><b>Example:<\/b><\/p>\n<p>See the query you are executing:<\/p>\n<p>select dest.*<br \/>\nfrom\u00a0 sys.dm_exec_requests as der<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cross apply sys.dm_exec_sql_text (der.sql_handle) as dest<br \/>\nwhere session_id = @@spid<\/p>\n<p>This\u00a0will return:<\/p>\n<p>dbid\u00a0\u00a0 objectid\u00a0\u00a0\u00a0 number encrypted <br \/>\n&#8212;&#8212; &#8212;&#8212;&#8212;&#8211; &#8212;&#8212; &#8212;&#8212;&#8212; <br \/>\nNULL\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a00<\/p>\n<p>text<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\nselect dest.*<br \/>\nfrom\u00a0\u00a0 sys.dm_exec_requests as der<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0cross apply sys.dm_exec_sql_text (der.sql_handle) as dest<br \/>\nwhere session_id = @@spid<\/p>\n<p>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\u00a0sys.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)<\/p>\n<p>&nbsp;<\/p>\n<p><em>Note:\u00a0 This is part of an ongoing project to write a book about all of the dynamic management views for <\/em><a href=\"http:\/\/www.red-gate.com\/\"><em>Redgate<\/em><\/a><em>.\u00a0It will be freely distributable once complete as an ebook.\u00a0Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.\u00a0<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Edit: Was reading Adam&#8217;s book tonight and discovered you can pass a plan handle to this object.\u00a0 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&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82022","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82022","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82022"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82022\/revisions"}],"predecessor-version":[{"id":84354,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82022\/revisions\/84354"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82022"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82022"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82022"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82022"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}