How to convert sql_handle to Sql text – SQL server

sys.dm_exec_sql_text(sql_handle | plan_handle) returns the text of the SQL batch that is identified by the specific sql_handle.

following are the few examples to convert sql_handle to Sql text

1. for finding blocking statements

select spid,lastwaittype,cmd,loginame,tx.text from sys.sysprocesses as st
cross apply sys.dm_exec_sql_text(st.sql_handle) as tx where blocked >0

2 . another simple script to convert the sql_handle to text.

select top 1 tx.text, * from sys.dm_exec_query_stats as st cross apply sys.dm_exec_sql_text ( st.sql_handle) as tx

Below are few DMV’s where we can get sql_handles.

1) sys.dm_exec_query_memory_grants
2) sys.dm_exec_requests
3) sys.dm_exec_query_stats