In this article, we will show you how to find the Dependencies of a Table in SQL Server using Management Studio and Query. It is beneficial to know the dependencies while you are Altering or dropping the table.
It is one of the most Frequent Questions in forums. To demonstrate the same we are using the HumanResources.Employee table present in the Adventure Works database.
Find the Dependencies of a Table in SQL Server
There are two approaches to find the Table Dependencies
Use SSMS to find the Dependencies of a Table
You can use SQL Server Management Studio to see Table Dependencies. For this, Goto the Object Explorer -> Expand the Adventure Works Database Folder -> and find the table for which you want to find the dependencies.
Right-click on the Table name and select the View Dependencies option from the context menu.
Once you select the View Dependencies option, the following window will open. This window has two options:
Objects that Depends on Employee: This SQL Server option will display all the tables, Functions, Views that are depending on the Employee Table.
Objects on which Employee Depends: This option displays all the tables on which this Employee table depends.
The following are the list of dependencies on the Employee table.
Use Query to find the Dependencies of a Table in SQL Server
You can use also use the query to find Table Dependencies.
Method 1 to find Table Dependencies in SQL Server
In this example, we are using the SP_DEPENDS stored procedure. It returns all the dependencies on the specified Object, including Tables, Views, Stored Procedures, Constraints, etc.
USE [AdventureWorks2014] GO EXEC sp_depends @objname = N'HumanResources.Employee' ;
It is another approach to finding the table dependencies
USE [AdventureWorks2014] GO SELECT referencing_id, referencing_schema_name, referencing_entity_name FROM sys.dm_sql_referencing_entities('HumanResources.Employee', 'OBJECT');
Method 3 to find Table Dependencies
USE [AdventureWorks2014] GO SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Employee%'
Method 4 to find Table Dependencies
USE [AdventureWorks2014] GO SELECT * FROM sys.sql_expression_dependencies A, sys.objects B WHERE referenced_id = OBJECT_ID(N'HumanResources.Employee') AND A.referencing_id = B.object_id GO
I think it is good to select the required columns rather than using SELECT *
USE [AdventureWorks2014] GO SELECT referenced_schema_name, referenced_entity_name, name, type_desc, create_date FROM sys.sql_expression_dependencies A, sys.objects B WHERE referenced_id = OBJECT_ID(N'HumanResources.Employee') AND A.referencing_id = B.object_id GO