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
|
1 2 3 4 |
-- 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
|
1 2 3 4 5 6 7 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 |
-- 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
|
1 2 3 4 5 6 7 8 9 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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
Leave a Reply