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 SQL Server Management Studio and SQL 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 SQL Server forums. To demonstrate the same we are using the HumanResources.Employe 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 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 is depending.

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, includes Tables, Views, Stored Procedures, Constraints, etc.

-- Query to find Table Dependencies in SQL Server is: 
USE [AdventureWorks2014]
GO
EXEC sp_depends @objname = N'HumanResources.Employee' ;

OUTPUT

It is another approach to find the table dependencies

-- Query to find Table Dependencies in SQL Server is: 
USE [AdventureWorks2014]
GO
SELECT referencing_id, 
       referencing_schema_name, 
       referencing_entity_name 
FROM sys.dm_sql_referencing_entities('HumanResources.Employee', 'OBJECT');

OUTPUT

Method 3 to find Table Dependencies

-- Query to find Table Dependencies in SQL Server is: 
USE [AdventureWorks2014]
GO

SELECT ROUTINE_SCHEMA,
       ROUTINE_NAME, 
       ROUTINE_TYPE,
       ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Employee%'

OUTPUT

Method 4 to find Table Dependencies

-- Query to find Table Dependencies in SQL Server is: 
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

OUTPUT

I think it is good to select the required columns rather than using SELECT *

-- Query to find Table Dependencies in SQL Server is: 
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

OUTPUT