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 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 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' ;
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');
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%'
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
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