List of tables referenced by stored procedure

There is a Dynamic Management Function introduced with SQL Server 2008 which help us to get the list of tables referenced by a stored procedure.

Below is the query:-

SELECT referenced_schema_name
, referenced_entity_name 'TableName'
, referenced_minor_name 'ColumnName'
, referenced_id
, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('<Schema>.<StoredProcedure>','OBJECT')

Note:- While specifying the stored procedure name please include schema name also, otherwise result will not display the dependencies.

Example:-

Create table 1

CREATE TABLE tbl1 (id INT)
GO

Create table 2

CREATE TABLE tbl2 (id INT)
GO

Create Stored Procedure which refer tbl1 & tbl2

CREATE PROC dbo.proc_test
AS
BEGIN

SELECT *
FROM tbl1 a
INNER JOIN tbl2 b
ON a.id = b.id

END
GO

Now time to figure out tables referenced by the stored procedure "proc_test" with the help of below query:-

SELECT referenced_entity_name 'TableName'
, referenced_minor_name 'ColumnName'
, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('dbo.proc_test','OBJECT')

Result of above query

TableName ColumnName referenced_class_desc
tbl1         id OBJECT_OR_COLUMN
tbl2         id OBJECT_OR_COLUMN


follow over facebook @ "SQL-Horizons"