declare @P1 int

exec sp_prepare @P1 output, N'@u int OUT', N'set @u = 888', 1

select @P1 P1

select text

from sys.dm_exec_sql_text(most_recent_sql_handle)

where session_id = 56

DBCC INPUTBUFFER(56)

declare @oo int

exec sp_execute @P1, @oo OUT 

select @oo oo

go

USE master;

GO

SELECT * FROM sys.dm_exec_requests

WHERE session_id = @@spid;

GO

declare @resultset TABLE (

[is_hidden] [bit] NOT NULL,

[column_ordinal] [int] NOT NULL,

[name] [nvarchar](128) NULL,

[is_nullable] [bit] NOT NULL,

[system_type_id] [int] NOT NULL,

[system_type_name] [nvarchar](128) NULL,

[max_length] [smallint] NOT NULL,

[precision] [tinyint] NOT NULL,

[scale] [tinyint] NOT NULL,

[collation_name] [nvarchar](128) NULL,

[user_type_id] [int] NULL,

[user_type_database] [nvarchar](128) NULL,

[user_type_schema] [nvarchar](128) NULL,

[user_type_name] [nvarchar](128) NULL,

[assembly_qualified_type_name] [nvarchar](4000) NULL,

[xml_collection_id] [int] NULL,

[xml_collection_database] [nvarchar](128) NULL,

[xml_collection_schema] [nvarchar](128) NULL,

[xml_collection_name] [nvarchar](128) NULL,

[is_xml_document] [bit] NOT NULL,

[is_case_sensitive] [bit] NOT NULL,

[is_fixed_length_clr_type] [bit] NOT NULL,

[source_server] [nvarchar](128) NULL,

[source_database] [nvarchar](128) NULL,

[source_schema] [nvarchar](128) NULL,

[source_table] [nvarchar](128) NULL,

[source_column] [nvarchar](128) NULL,

[is_identity_column] [bit] NULL,

[is_part_of_unique_key] [bit] NULL,

[is_updateable] [bit] NULL,

[is_computed_column] [bit] NULL,

[is_sparse_column_set] [bit] NULL,

[ordinal_in_order_by_list] [smallint] NULL,

[order_by_is_descending] [bit] NULL,

[order_by_list_length] [smallint] NULL,

[tds_type_id] [int] NOT NULL,

[tds_length] [int] NOT NULL,

[tds_collation_id] [int] NULL,

[tds_collation_sort_id] [tinyint] NULL

)

insert into @resultset

exec sp_describe_first_result_set @tsql = N'SELECT * FROM sys.dm_exec_requests WHERE session_id = @@spid'

select * from @resultset

declare @phandletype nvarchar(max)

declare @shandletype nvarchar(max)

select @phandletype = r.system_type_name from @resultset r where r.name = 'plan_handle'

select @shandletype = r.system_type_name from @resultset r where r.name = 'sql_handle'

declare @exec nvarchar(max) = '

declare @pHandle ' + @phandletype + '

declare @sHandle ' + @shandletype + '

SELECT @pHandle = r.plan_handle, @sHandle = r.sql_handle FROM sys.dm_exec_requests r WHERE r.session_id = @@spid

select * from sys.dm_exec_query_plan(@pHandle)

select * from sys.dm_exec_sql_text(@sHandle)'

exec (@exec)

--SELECT * FROM sys.dm_exec_cached_plans

 --sys.dm_exec_sql_text

 SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) order by cp.plan_handle

 SELECT * FROM sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = @@spid

 DBCC FREEPROCCACHE

 select * from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(cp.plan_handle)

 select st.text, qs. sql_handle, qs.plan_handle

from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st

select st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, cp.memory_object_address

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

order by cp.usecounts desc

go

select st.text, cp.plan_handle, do.usecounts, do.cacheobjtype,

do.memory_object_address

from sys.dm_exec_cached_plans cp

cross apply

sys.dm_exec_sql_text(cp.plan_handle) st

cross apply

sys.dm_exec_cached_plan_dependent_objects(cp.plan_handle) do

go