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:

First, let’s create some objects and dependencies between them using the script from below:


USE EmployeeDB

  ,FirstName VARCHAR(100)
  ,LastName VARCHAR(150)
  ,Address VARCHAR(250)

CREATE TABLE EmployeeAddress (
  ,City VARCHAR(120)
  ,PostalCode INT
  ,EmployeeAddressID INT FOREIGN KEY REFERENCES Employee(AddressID)

CREATE PROCEDURE sp_GetEmployeeAddress
  FROM Employee

FROM EmployeeAddress

  FROM Employee
  INNER JOIN EmployeeAddress ON Employee.AddressID = EmployeeAddress. EmployeeAddressID

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee]
PRINT 'Data were successfully entered'


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';

View SQL dependency using the sp_depends stored procedure

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';

View the SQL objects on which specified object in the sp_depends procedure depends

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:

The "Object does not reference any object, and no objects reference it.” message

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';

The sp_depends procedure does not show triggers

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:

FROM sys.dm_sql_referencing_entities('dbo.EmployeeAddress', 'Object')

Will return the SQL dependency like in the image below:

View SQL dependency using the sys.dm_sql_referencing_entities function


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:

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:

View SQL dependency using the sys.dm_sql_referenced_entities function

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:

Difference between referenced vs referencing

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:

FROM sys.dm_sql_referencing_entities('EmployeeAddress', 'Object')

The results set will be empty, as shown below:

Empty results set

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:

    referenced_entity_name ,
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:

View all all objects on which specified object depends on

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,  
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 all objects that depend on specified object

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 View Dependencies option under the Object Explorer context menu

The Object Dependencies window will appear, and by default, the Object that depended on radio button will be selected:

Object Dependencies window showing object that depend on a table

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:

Object Dependencies window showing object on which  a table depends

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:

Integration of ApexSQL Analyze in SSMS/VS

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:

Show dependencies command to view all dependencies in a database

The Dependency viewer window will appear:

Dependency viewer pane in ApexSQL Analyze

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

Filtering objects using the Object browser pane

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:

Dependencies option in the application

More about this SQL dependency viewer and how it can be used can be found in the articles below:


March 25, 2020