Skip to content
Related Articles

Related Articles

Check the Dependencies of a Table in SQL Server
  • Last Updated : 07 Dec, 2020

As a SQL DBA, you might need to find the Dependencies of a Table in SQL Server using SQL Server Management Studio or SQL Query. It will be useful to have information about the dependencies while altering or dropping any table.

To find Table Dependencies in SQL Server using SQL Server Management Studio :
Step-1 :
Expand Database, Expand Tables, Right click on the table name.

Step-2 : 
Click on View Dependencies. 
 

To find Table Dependencies in SQL Server Using SQL Quires.
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-1 :

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

Output :

name type
dbo.usp_FetchStatistics stored procedure
dbo.usp_PostStatistics_Update stored procedure
dbo.usp_Update_theStatistics stored procedure

Approach-2 :
Query –

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

Example-1 :

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

Output :

referencing
_schema_name
referencing
_entity_name
referencing
_id
referencing
_class
referencing
_class_desc
is_caller
_dependent
dbo usp_FetchStatistics 597577167 1 OBJECT_OR_COLUMN 0
dbo usp_PostStatistics
_Update
581577110 1 OBJECT_OR_COLUMN 0
dbo usp_Update
_theStatistics
565577053 1 OBJECT_OR
_COLUMN
0

Approach-3 :
Query –

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

Example-1 :

use SQL_DBA

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

Output :

ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE
dbo usp_Update_theStatistics PROCEDURE
dbo usp_PostStatistics_Update PROCEDURE
dbo usp_FetchStatistics PROCEDURE

Approach-4 :
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-1 :

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 :

referenced_id referenced_database_name referenced_schema_name name
613577224 SQL_DBA dbo usp_Update_theStatistics
613577224 SQL_DBA dbo usp_PostStatistics_Update
613577224 SQL_DBA dbo usp_FetchStatistics
My Personal Notes arrow_drop_up
Recommended Articles
Page :