SQL dependency viewer

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
• 3^rd 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

sp_depends

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:

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.

sys.dm_sql_referencing_entities

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:

sys.dm_sql_referenced_entities

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

sys.sql_expression_dependencies

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:

• ApexSQL Analyze for a first-time user
• How to customize SQL database dependency diagrams