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