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 |
sql | SQL Server > sqlsrv | T-SQL >