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 |
Last Updated :
08 Apr, 2022
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...