如何使用扩展事件(SQL Server 2012)告诉我存储过程中何时使用了某些表。我想删除一些表,所以我想知道使用这些表的存储过程是否正在运行。
代码示例设置支持对象。当您运行这些存储过程(ListEmp和ListProd)时,我希望它们被选中,因为它们包含我正在跟踪的表(Employees和Products)。
请注意,我还尝试使用sp_statement_starting事件:
-- setup supporting objects
CREATE TABLE Employees (Col1 VARCHAR(10));
CREATE TABLE Products (Col1 VARCHAR(10));
GO
INSERT INTO Employees(Col1) VALUES ('Bob');
INSERT INTO Products(Col1) VALUES ('Bolts');
GO
CREATE PROCEDURE ListEmp
AS
SELECT * FROM Employees;
GO
CREATE PROCEDURE ListProd
AS
SELECT * FROM Products;
GO
-- create extended event (that is not doing what I want)
CREATE EVENT SESSION XE_TrackEmployeesAndProductsTables
ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
SET collect_statement=1
ACTION
(
sqlserver.database_name,
sqlserver.sql_text
)
WHERE
(
sqlserver.like_i_sql_unicode_string(sqlserver.sql_text,N'%Employees%')
OR sqlserver.like_i_sql_unicode_string(sqlserver.sql_text,N'%Products%')
)
);
ALTER EVENT SESSION XE_TrackEmployeesAndProductsTables ON SERVER STATE=START;
GO
-- run procs that I want to be picked up by my session (but aren't)
EXEC ListEmp;
EXEC ListProd;
-- cleanup
DROP EVENT SESSION XE_TrackEmployeesAndProductsTables ON SERVER;
DROP PROC ListEmp;
DROP PROC ListProd;
DROP TABLE Employees;
DROP TABLE Products;发布于 2021-03-12 06:11:52
我只会将这段代码添加到您想要跟踪的任何已存储进程的开头:
declare @msg nvarchar(128) = concat('Stored proc ',OBJECT_NAME(@@PROCID),' run.')
EXEC master..sp_trace_generateevent @event_class = 82, @userinfo = @msg; 您可以使用如下所示的XEvent会话来跟踪它:
CREATE EVENT SESSION [UserEvent] ON SERVER
ADD EVENT sqlserver.user_event
ADD TARGET package0.event_file(SET filename=N'UserEvent')您可以像这样标识引用静态SQL中的表的所有过程:
select distinct object_name(d.object_id) referencing_object, SCHEMA_NAME(o.schema_id) referencing_object_schema
from sys.sql_dependencies d
join sys.objects o
on o.object_id = d.object_id
where d.referenced_major_id = object_id('Sales.SalesOrderHeader')
and o.type = 'P'https://stackoverflow.com/questions/66590859
复制相似问题