sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | temp scripts

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
Comments