Get statement from sqlhandle

I reverse-engineered a script I found on MSDN to get the actual SQL statement/sproc from a SQL handle. Note that the sqlhandle identifier is not passed as a literal.

-- http://msdn.microsoft.com/en-us/library/ms181929.aspx?PHPSESSID=ev0v34i8u80gkmn37sd8r65363 select sql_handle as Handle, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS Text from sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st where sql_handle = --0x04000D00E3572A56542E4601CE9E00010100001000000000

Comments

[ipad 2 keyboard]

This is very useful. I use this especially to retrieve sql statements from deadlock graph xml.

[Tallarico]

Glad you found it helpful!

Thanks for visiting.