Find the Dependencies of a Table in SQL Server

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