How to find all dependencies of a table in Sql Server?

First of all we shouldn’t use the system stored procedure sp_depends for this as it is not reliable. For more details on sp_depends you can refer to the article sp_depends results are not reliable.

For this in Sql server 2008 new Dynamic Management Function sys.dm_sql_referencing_entities is introduced. This dynamic management function provides all the entities in the current database that refer to the specified table.

Let us understand this with an example. First create a table, then a stored procedure which references the table.

 USE DemoSQLHints
CREATE TABLE dbo.Employee
 (Id int IDENTITY(1,1), FirstName NVarchar(50), LastName NVarchar(50))
 INSERT INTO dbo.Employee(FirstName, LastName)
CREATE PROCEDURE dbo.GetEmployeeDetails
 SELECT * FROM dbo.Employee

Now we can use a script like below to find all entities in the current database that refer to the table dbo.Employee:

SELECT referencing_schema_name, referencing_entity_name, 
 referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities ('dbo.Employee', 'OBJECT')

Note: 1) This Dynamic Management Function is introduced as a part of Sql Server 2008. So above script works in Sql Server version 2008 and above.
2) While specifying the table name please include schema name also, otherwise result will not display the dependencies.

You may also like to read my other articles:

How to find all the objects referenced by the stored procedure in Sql Server?

Please correct me if my understanding is not correct. Comments are always welcome.

6 thoughts on “How to find all dependencies of a table in Sql Server?

  1. Many people are still using sys.sql_dependencies which isn’t very reliable. The results are very inconsistent.

    As mentioned above better approach is to use



    Alternatively tools such as SQL Negotiator Pro, Redgate etc will generate this for you using a GUI

  2. This will only show objects from within the database that you’re on, ie. DemoSQLHints

    If you reference the Employee table from another database, the dependency will not show up in dm_sql_referencing_entities

Leave a Reply

Your email address will not be published. Required fields are marked *