Function to Join CahcedPlan, Query Plan and Sql_text

by

The cache plan handle can be found by querying

select
*
from
sys.dm_exec_cached_plans

How do I get the sql text and Query Plan for this plan handle?

create
function returnSQL(@planhandle varbinary(max))

returns
table

As

return

select
sql.text,pc.usecounts,pc.cacheobjtype,pc.objtype,pc.size_in_bytes,

Pq.query_plan

From

Sys.dm_exec_sql_text(@planhandle)
as
sql
cross
join

Sys.dm_exec_query_plan(@planhandle)
as pq join

Sys.dm_exec_cached_plans
as pc on pc.plan_handle = @planhandle;

We will use the about funtion to return the selected columns by passing Plan Handle

select
*
from returnSQL(0x0500050021814A6FB8805C09000000000000000000000000)

Below is the output which shows sqltext,queryplan.