Open In App

Check the Dependencies of a Table in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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 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-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

Last Updated : 08 Apr, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads