List of stored procedures depend on a table

There is a Dynamic Management Function introduced with SQL Server 2008 which help us to get the list of stored procedures depend on a table.

Below is the query:-

SELECT referencing_schema_name
, referencing_entity_name 'StoredProcedureName'
, referencing_class_desc
FROM sys.dm_sql_referencing_entities('<schema>.<TableName>','OBJECT')

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

Example:-

Create table

CREATE TABLE tbl (id INT)
GO

Create Stored Procedure which refer table "tbl"

CREATE PROC dbo.proc_test
AS
BEGIN
 
SELECT *
FROM tbl1

END 
GO

Lets create one another stored procedure which refer table "tbl"

CREATE PROC dbo.proc_test2
AS
BEGIN
 
INSERT INTO tbl VALUES (1)

END 
GO

Now time to figure out name of stored procedures depend on table "tbl" with the help of below query:-

SELECT referencing_schema_name
, referencing_entity_name 'StoredProcedureName'
, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.tbl','OBJECT')

Result of above query

referencing_schema_name StoredProcedureName referencing_class_desc
dbo                                          proc_test                         OBJECT_OR_COLUMN
dbo                                          proc_test2                 OBJECT_OR_COLUMN


follow over facebook @ "SQL-Horizons"