Check the Dependencies of a Table in SQL Server
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 |
Contact Us