Check the Dependencies of a Table in SQL Server

Sometimes there is a need to find the dependencies of a table in SQL Server using SQL Server Management Studio or SQL Query. It is useful to have information about the dependencies while altering or dropping any table.

In this article, we will learn how to find table dependencies in SQL Server using SQL Server Management Studio and SQL queries

Find Dependencies of a Table Using SQL Server Management Studio

Follow the steps below, to check dependencies of a table using SQL Server Management Studio.

Step-1: Expand Database, Expand Tables, Right click on the table name.

Step-2: Click on View Dependencies.  

Find Table Dependencies in SQL Server Using SQL Queries

We can also check table dependencies in SQL Server using SQL Queries.

Approach 1: Using the SP_DEPENDS stored procedure

It will return all the dependencies on the specified Object, includes Tables, Views, Stored Procedures, Constraints, etc.

Query:

Use DatabaseName ;
EXEC sp_depends @objname = N'ObjectName' ;

Example:

Use SQL_DBA ;
EXEC sp_depends @objname = N'[dbo].[tbl_Errors_Stats]' ;

Output:

Approach 2: Using sys.dm_sql_referencing_entities

Query:

Use DatabaseName ;
SELECT * FROM sys.dm_sql_referencing_entities('ObjectName', 
                      'OBJECT') ;

Example

use SQL_DBA ;
SELECT * FROM sys.dm_sql_referencing_entities('[dbo].[tbl_Errors_Stats]',
                                   'OBJECT') ;

Output :

Approach 3: Using INFORMATION_SCHEMA.ROUTINES

Query:

SELECT ROUTINE_SCHEMA,
      ROUTINE_NAME,  
      ROUTINE_TYPE,
      ROUTINE_DEFINITION  
FROM INFORMATION_SCHEMA.ROUTINES  
WHERE ROUTINE_DEFINITION LIKE '%ObjectName%'

Example:

use SQL_DBA

SELECT ROUTINE_SCHEMA,
ROUTINE_NAME, 
ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tbl_Errors_Stats%'

Output :

Approach 4: Using sys.sql_expression_dependencies

Query:

SELECT *
FROM sys.sql_expression_dependencies A, sys.objects B
WHERE referenced_id = OBJECT_ID(N'ObjectName') AND  
 A.referencing_id = B.object_id  

GO

Example:

use SQL_DBA

SELECT referenced_id, referenced_database_name, referenced_schema_name, name
FROM sys.sql_expression_dependencies A, sys.objects B
WHERE referenced_id = OBJECT_ID(N'tbl_Errors_Stats') AND
A.referencing_id = B.object_id 

GO

Output :