In the relational SQL Server database systems, objects can have different relationships (SQL dependency) with each other. Objects such as views, stored procedures, functions can depend on other objects and understanding SQL dependency between objects can be very important in situations when there is a need to update or delete objects that depend upon other objects.
In this article, a couple of ways to identify SQL dependency between objects will be introduced:
- The sp_depends system stored procedure
- The sys.dm_sql_referencing_entities dynamic management function
- The sys.dm_sql_referenced_entities dynamic management function
- The sys.sql_expression_dependencies system catalog view
- The SQL Server Management Studio (SSMS) feature – View Dependencies
- 3rd party dependency viewer – ApexSQL Analyze
First, let’s create some objects and dependencies between them using the script from below:
CREATE DATABASE EmployeeDB; GO USE EmployeeDB GO CREATE TABLE Employee ( AddressID INT PRIMARY KEY IDENTITY(1, 1) ,FirstName VARCHAR(100) ,LastName VARCHAR(150) ,Address VARCHAR(250) ) GO CREATE TABLE EmployeeAddress ( ID INT NOT NULL IDENTITY(1, 1) ,City VARCHAR(120) ,PostalCode INT ,EmployeeAddressID INT FOREIGN KEY REFERENCES Employee(AddressID) ) GO CREATE PROCEDURE sp_GetEmployeeAddress AS BEGIN SELECT FirstName ,LastName ,Address FROM Employee END GO CREATE VIEW v_Address AS SELECT ID ,City ,PostalCode ,EmployeeAddressID FROM EmployeeAddress GO CREATE PROCEDURE sp_GetEmployeeCity AS BEGIN SELECT Employee.FirstName ,Employee.Lastname ,EmployeeAddress.City FROM Employee INNER JOIN EmployeeAddress ON Employee.AddressID = EmployeeAddress. EmployeeAddressID END GO CREATE TRIGGER trgAfterInsert ON [dbo].[Employee] FOR INSERT AS PRINT 'Data were successfully entered' GO
This system stored procedure shows information of SQL objects, e.g., procedures, views that depend on other objects specified as an input parameter (table name, view name) of the sp_depends procedure as well as objects that specified object depends on.
For example, if a table name is specified as the argument, then the views and procedures that depend on the specified table will be shown in the result set:
EXECUTE sys.sp_depends @objname = N'Employee';
If a procedure or view name is specified as the argument, then the tables and views on which the specified argument depends on are shown:
EXECUTE sys.sp_depends @objname = N'dbo.sp_GetEmployeeAddress';
Beside the table name in the results set, the name of the columns within that table on which specified stored procedure depends will be shown.
Be aware that in some cases, the sp_dependes stored procedure doesn’t work as expected. In a situation when an object, let’s say a table, is deleted and recreated again, the following message in results set will be shown when the above script is executed:
“Object does not reference any object, and no objects reference it.”
The same message will appear if during creating of objects, for example, a stored procedure is created first and then a table on which created stored procedure depends:
Also, the sp_depends SP does not show triggers. In the script for creating objects and dependencies, the trgAfterInsert trigger was created on the Employee table, but when executing the below code, the trigger won’t be listed in the results grid:
EXECUTE sys.sp_depends @objname = N'Employee';
Keep in mind that this system stored procedure will most likely be removed from the future SQL Server versions.
Using this dynamic management function will return all objects (views, stored procedures, functions) that depend on the specified object in the sys.dm_sql_referencing_entities function as the argument.
Executing this code:
SELECT * FROM sys.dm_sql_referencing_entities('dbo.EmployeeAddress', 'Object')
Will return the SQL dependency like in the image below:
This dynamic management function will show information about the objects on which specified object in the sys.dm_sql_referenced_entities function depends on.
Execute the code below:
SELECT * FROM sys.dm_sql_referenced_entities('dbo.sp_GetEmployeeCity', 'Object')
The results grid under the referenced_entity_name column will show table names on which the sp_GetEmployeeCity procedure depends on, also, under the referenced_minor_name column the columns of the referenced entity are shown:
Difference between referenced and referencing
A SQL dependency created between two objects when one object appears by name inside a SQL statement as a part of another object, that object is called a referenced entity, and an object that has the SQL expression is known as a referencing entity.
For example, the sp_GetEmployeeAddress stored procedure is a referencing entity, and the Employee table inside this stored procedure is a referenced entity:
Tips and tricks when using stored procedures
In order for the sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities dynamic management functions to work properly, two-part name convection must be specified as the object name. That means, when specifying an object name in these two functions, the schema name must be the part of the object name.
For example, if the object name is specified without schema name and the following code is executed:
SELECT * FROM sys.dm_sql_referencing_entities('EmployeeAddress', 'Object')
The results set will be empty, as shown below:
Also, an empty result set will be returned when:
- The specified object doesn’t exist in the database
- An invalid parameter is specified
- The specified object doesn’t reference any objects
- A system object is specified
This view returns all dependencies on used-defined objects (entity) in the current database.
With this view, objects that depend on a specified object can be shown and also, objects on which specified object depends on can be shown.
To find all objects on which specified object depends on, execute the code below:
SELECT referenced_entity_name , referenced_class_desc FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID('sp_GetEmployeeCity');
In the results grid, all objects on which the sp_GetEmployeeCitytored procedure depends on will be listed:
To view all objects that depend on the specified object, use this script:
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name, OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_class_desc, referenced_entity_name FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referenced_id = OBJECT_ID(N'EmployeeAddress');
View Dependencies feature
One more solution to view SQL dependency between the SQL objects is to use the SSMS feature called View Dependencies.
In Object Explorer, under a database, select an object for which you want to view SQL dependency, right-click and, from the context menu, choose the View Dependencies command:
The Object Dependencies window will appear, and by default, the Object that depended on radio button will be selected:
In the Dependencies pane, all objects that depend on the selected object will be listed; in this case, the EmployeeAddress table.
If the Object on which radio button is selected, then, in the Dependencies pane, objects on which selected object (in our case EmployeeAddress table) depends on will be shown:
Third-party dependency viewer
ApexSQL Analyze is an application that can be used as the graphical SQL dependency viewer. All objects in ApexSQL Analyze are present with different shapes and colors to improve their visual look and readability.
ApexSQL Analyze is a stand-alone tool, but it can also be integrated into SSMS and Visual Studio as an add-in:
This application can be downloaded from the ApexSQL download page.
To view dependencies using ApexSQL Analyze, in Object Explorer, right-click a database and from the context menu choose the Show dependencies command:
The Dependency viewer window will appear:
ApexSQL Analyze offers many options for filtering, options for manipulating objects and their graphical appearance. Under the Object browser pane, it can be specified which objects in the dependency graph will be shown (e.g., views, tables, triggers, etc.):
If some object in the dependency graph (e.g., Employee table) is selected, then all objects on which the selected object depends on and objects that depend on the selected object will be shown in the Dependencies pane:
More about this SQL dependency viewer and how it can be used can be found in the articles below:
March 25, 2020