How To Get Object Dependencies In SQL Server

Programming languages have objects that rely on another object. A rather similar principle applies to a database. Multiple types of object dependencies exist in a database including relationships between tables, primary-foreign keys, constraints and schema bindings. Therefore Object dependencies become critical in a growing database and have to be handled with caution.

As an example, a table with a foreign key dependent on another table’s primary key. A view definition is dependent on a table. Stored procedure dependent on another stored procedure to get the data. A table could be dependent on an index for faster retrieval of rows.

Unexpected side effects could occur if an object is dropped in SQL Server without analysing its dependencies. Certainly beneficial to find out those objects that depend on [object] and objects on which [object] depends. To be assured that dropping an object would not make database design inconsistent.

Let’s execute the below script to create objects which are interdependent on each other as shown in the following image. Using these objects we will try to find their dependency using SQL Server query and graphical view.

USE tempdb;
GO

--Dropping existing objects in perticular order to avoid depedencies error
IF OBJECT_ID('EmpInfo','V') IS NOT NULL DROP VIEW EmpInfo
IF OBJECT_ID('Emp','U')     IS NOT NULL DROP TABLE Emp
IF OBJECT_ID('Dept','U')    IS NOT NULL DROP TABLE Dept
IF OBJECT_ID('Dept_A','P')  IS NOT NULL DROP PROCEDURE Dept_A
IF OBJECT_ID('Dept_B','P')  IS NOT NULL DROP PROCEDURE Dept_B

CREATE TABLE Dept (Id   INT PRIMARY KEY
                  ,Name VARCHAR(50));

--Foreign key Reference
CREATE TABLE Emp (
                  Id   INT         PRIMARY KEY
                 ,Name VARCHAR(20)
		 ,DeptId	   INT FOREIGN KEY REFERENCES Dept(Id)
                 );
GO

--View-dependent on Emp table.
CREATE VIEW EmpInfo  AS 
SELECT Id, Name FROM Emp;	
GO

--Stored Proc dependent on Dept & Emp table.
CREATE PROCEDURE Dept_A AS 
BEGIN
	SELECT * FROM Dept INNER JOIN Emp 
	ON Dept.Id = Emp.DeptId;
END
GO

--Stored Proc dependent on Dept_A another Stored Proc.
CREATE PROCEDURE Dept_B AS 
BEGIN
	EXEC Dept_A;
END

Above query creates following objects and their dependency as shown in following.

Find Object Dependencies In SQL Server Management Studio (SSMS)

1.Navigate to Object Explorer in SSMS or Use the F8 keyboard shortcut to open Object Explorer.

2.Expand the database name tree, and navigate to the object name.

3.Right click on highlighted object name (table/ view/ stored procedure) and select View Dependencies.

4.Using the above steps we can find (table/ view/ stored procedure/ function/ trigger) and other object’s dependency using a graphical tree view.

Find Object Dependencies Using DMF (Dynamic Management Functions)

Using the following dynamic management function we can find object dependencies in SQL Server. Important to note that specifying schema name in the first parameter with object name is mandatory in these DMFs. For an example, use dbo.Emp instead of Emp.

1. sys.dm_sql_referenced_entities

2. sys.dm_sql_referencing_entities

Before we learn more about these DMFs, it is important to understand following concepts.

1.

Referenced Entity

When an object appears within a SQL statement then that object is classified as Referenced Entity. In our example, the Dept table is Referenced Entity as it is referenced in Dept_A view. Using sys.dm_sql_referenced_entities DMF we can find referenced entities on an object.

SELECT * FROM sys.dm_sql_referenced_entities('dbo.Emp','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.Dept','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.EmpInfo','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.Dept_A','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.Dept_B','Object');

In the following illustration, we can see that EMP and DEPT appear within Dept_A procedure. Thus these are referenced entities.

2

Referencing Entity

The SQL statement which contains a reference to other object is classified as a referencing entity. Is our case, Dept_A view is referencing entity in which Dept table object appears. Using sys.dm_sql_referencing_entities DMF we can find referenced Entity.

SELECT * FROM sys.dm_sql_referencing_entities('dbo.Emp','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Dept','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.EmpInfo','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Dept_A','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Dept_B','Object');

Furthermore, In the following illustration, we can see that Dept_A procedure and EmpInfo view contains a reference to Emp table.

Find Object Dependencies using sp_depends

Although sp_depends is a deprecated feature, we can still use it. Microsoft has planned to remove this system stored procedure in upcoming versions thus it is not recommended to use this for production use cases. However, we have observed that it does produce wrong results in some tricky dependencies. I would recommend using sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities following Microsoft’s docs advice.

Use tempdb;
Go

EXEC sp_depends @objname = 'Dept';

From Microsoft Docs

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Summary

Above all, we can learned how to find object dependencies in SQL Server using graphical method and queries. Furthermore, this can help us to study the objects depedency before we take a decision to drop it from database. If you like this post you may read Tech-Recipes Database Posts.