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.
Before we learn more about these DMFs, it is important to understand following concepts.
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.
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.
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.