SQL Handle and Plan Handle to Text – Quick Reference

SQL Handle to Text – Quick Reference

DECLARE @pStartOffSet INT
DECLARE @pEndOffSet INT
DECLARE @pSQLHandle VARBINARY(64)

SELECT @pSQLHandle = 0x030008001374fc0232620901c7a300000100000000000000
,@pStartOffSet = 1788,@pEndOffSet = 3042

SELECT
    SUBSTRING(st.text, (@pStartOffSet/2)+1,
        ((CASE @pEndOffSet
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE @pEndOffSet
         END - @pStartOffSet)/2) + 1) AS statement_text
FROM sys.dm_exec_sql_text(@pSQLHandle) AS st

Plan Handle to XML Plan:

SELECT plantext.objectid, OBJECT_NAME(plantext.objectid), [text], cp.size_in_bytes, sqlplan.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) plantext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) sqlplan
WHERE  plantext.objectid = object_id('schema.table')