CODEWITHSUNDEEP
sql-serverextended-events
Craig
Craig

Reputation: 4229

How to use extended events to track tables used in stored procedures being run

How do I use extended events (SQL Server 2012) to tell me when certain tables are used in stored procedures. I want to drop some tables and so I want to know if the stored procedures the use those tables are actually being run.

The code sample sets up the supporting objects. And creates a session that I expect to work but doesn't. When you run those stored procedures (ListEmp and ListProd), I want them picked up because they contain the tables I am tracking (Employees and Products).

Note, I also tried using the sp_statement_starting event:

-- 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;

Upvotes: 0

Views: 1212

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

I would just add this to the beginning of any stored proc you want to track:

declare @msg nvarchar(128) = concat('Stored proc ',OBJECT_NAME(@@PROCID),' run.')
EXEC master..sp_trace_generateevent  @event_class = 82, @userinfo = @msg;

Which you can track with an XEvent session like this:

CREATE EVENT SESSION [UserEvent] ON SERVER 
ADD EVENT sqlserver.user_event
ADD TARGET package0.event_file(SET filename=N'UserEvent')

You can identify all the procedures that reference a table in static SQL like this:

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'

Upvotes: 1

Related Questions