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

Question

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;

Perhaps the `Audit Database Object Access Event` might be better one to track, you need to switch on auditing — Charlieface, Mar 11 '21 at 22:11

David Browne - Microsoft · Accepted Answer · 2021-03-11T22:43:52.573

1

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'

edited Mar 11 '21 at 22:43

answered Mar 11 '21 at 22:11

David Browne - Microsoft

80,331
6
39
67

Thanks, I didn't know that. But in this case I have a list of tables (not procs) that I want to track – Craig Mar 11 '21 at 22:15
But you asked: "I want to know if the stored procedures the use those tables are actually being run" – David Browne - Microsoft Mar 11 '21 at 22:20
Yes, I'm only interested in procs that include "Employees" or "Products" AND ALSO are being run. If a proc isn't used anymore, I want to ignore that. And I don't have a list of the procs -- just a list of the tables. I could get a list of the procs, but there is a small number of tables and lots of procs (that might or might not be used) – Craig Mar 11 '21 at 22:29
If I could add "sp_trace_generateevent" to one of the tables, then that would work. And I would know when those tables were being used – Craig Mar 11 '21 at 22:30
That can't track table access. Audit is what you want for that. – David Browne - Microsoft Mar 11 '21 at 22:44
Thanks, David. I'll use SQL Audit instead of Extended Events. – Craig Mar 18 '21 at 09:27

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

1 Answers1