Check Dependent Objects

In order to check the dependent objects being referenced by a specified object, e.g. to find the referenced tables of a stored procedure, previously we can used the sp_depends system builtin stored procedure. However, if the dependent objects (e.g. table) are created after the referencing object (e.g. stored procedure), sp_depends cannot find out such dependency. Started from SQL2008, there are two new DMFs (sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities) which overcome such problem. Let's have a try in the following example:

USE TempDB
GO

DROP TABLE TestTable
GO
CREATE TABLE dbo.TestTable
( ID INT,
Name VARCHAR(100))
GO

-- referencing usp1 NOT created yet
DROP PROC usp2
GO
CREATE PROCEDURE dbo.usp2
AS
EXEC dbo.usp1
GO

DROP PROC usp1
GO
CREATE PROCEDURE dbo.usp1
AS
SELECT ID, Name
FROM TestTable
GO

SELECT * FROM sys.dm_sql_referencing_entities ('dbo.usp1', 'OBJECT');
SELECT * FROM sys.dm_sql_referenced_entities ('dbo.usp1', 'OBJECT');
SELECT * FROM sys.dm_sql_referenced_entities ('dbo.usp2', 'OBJECT');