Skip to content
Related Articles

Related Articles

Improve Article

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 :

nametype
dbo.usp_FetchStatisticsstored procedure
dbo.usp_PostStatistics_Updatestored procedure
dbo.usp_Update_theStatisticsstored 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
dbousp_FetchStatistics5975771671OBJECT_OR_COLUMN0
dbousp_PostStatistics
_Update
5815771101OBJECT_OR_COLUMN0
dbousp_Update
_theStatistics
5655770531OBJECT_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_SCHEMAROUTINE_NAMEROUTINE_TYPE
dbousp_Update_theStatisticsPROCEDURE
dbousp_PostStatistics_UpdatePROCEDURE
dbousp_FetchStatisticsPROCEDURE

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_idreferenced_database_namereferenced_schema_namename
613577224SQL_DBAdbousp_Update_theStatistics
613577224SQL_DBAdbousp_PostStatistics_Update
613577224SQL_DBAdbousp_FetchStatistics

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :