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. This is very useful to know the dependencies while you are Altering, or dropping the table.

This is one of the most Frequent Question in SQL Server forums. To demonstrate the same w are using the HumanResources.Employe table present in the Adventure Works database.

Find the Dependencies of a Table in SQL Server

There are two approach to find the Table Dependencies in SQL Server

Use SSMS to find the Dependencies of a Table in SQL Server

You can use SQL Server Management Studio to find the Table Dependencies. For this, Got o 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 as we shown below.

Once you select the View Dependencies option, following window will be opened. 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 will display all the table on which this Employee table is depending.

Following are the list of dependencies on Employee table.

Use Query to find the Dependencies of a Table in SQL Server

You can use also use T-SQL query to find the Table Dependencies.

Method 1 to find Table Dependencies in SQL Server

In this example we are using the SP_DEPENDS stored procedure. This will return all the dependencies on the specified Object, It includes Tables, Views, Stored Procedures, Constraints etc.

SQL CODE

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

OUTPUT

Method 2 to find Table Dependencies in SQL Server

SQL CODE

-- 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 in SQL Server

SQL CODE

-- 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 in SQL Server

SQL CODE

-- 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 *

SQL CODE

-- 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

Thank You for Visiting Our Blog