Find Dependencies in SQL Server: sql_expression

In SQL Server, you can use the sys.sql_expression_dependencies system catalog view to return all dependencies on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules.

You can use this view to:

Return entities that depend on a given entity
Return entities on which a given entity depends

So for example, you could use it to return all objects that reference a specific table. You could also use it to return all objects that a specific stored procedure references within its code.

Specifically, the sys.sql_expression_dependencies view reports dependency information for the following entities:

Schema-bound entities.
Non-schema-bound entities.
Cross-database and cross-server entities. Entity names are reported; however, entity IDs are not resolved.
Column-level dependencies on schema-bound entities. Column-level dependencies for non-schema-bound objects can be returned by using sys.dm_sql_referenced_entities.
Server-level DDL triggers when in the context of the master database.

Example 1 – All Columns Returned

Here’s a quick example that selects all columns from sys.sql_expression_dependencies. This shows us what data is actually returned in the view, and we can use any of these columns in our queries in order to return just the data we’re interested in.

SELECT TOP(1) * 
FROM sys.sql_expression_dependencies;

Result (using vertical output):

referencing_id            | 114099447
referencing_minor_id      | 0
referencing_class         | 1
referencing_class_desc    | OBJECT_OR_COLUMN
is_schema_bound_reference | 0
referenced_class          | 1
referenced_class_desc     | OBJECT_OR_COLUMN
referenced_server_name    | NULL
referenced_database_name  | NULL
referenced_schema_name    | dbo
referenced_entity_name    | Client
referenced_id             | 434100587
referenced_minor_id       | 0
is_caller_dependent       | 0
is_ambiguous              | 0

This example uses vertical output so as to make it easier to see the column names without having to scroll horizontally. Therefore, the column names are listed on the left and their respective values are on the right.

Also, for the sake of brevity I used TOP(1) to limit the results to just the first row.

Example 2 – Find Entities that Depend on an Entity

To find objects that depend on a given entity, use that entity’s referencing_id when selecting from the view.

Example:

SELECT
    referenced_server_name AS [Referenced Server],
    referenced_database_name AS [Referenced DB],
    referenced_schema_name AS [Referenced Schema],
    referenced_entity_name AS [Referenced Entity],
    referenced_class_desc AS [Referenced Entity Class] 
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('uspGetClient');

Result:

+---------------------+-----------------+---------------------+---------------------+---------------------------+
| Referenced Server   | Referenced DB   | Referenced Schema   | Referenced Entity   | Referenced Entity Class   |
|---------------------+-----------------+---------------------+---------------------+---------------------------|
| NULL                | NULL            | dbo                 | Client              | OBJECT_OR_COLUMN          |
| NULL                | NULL            | NULL                | clientcode          | TYPE                      |
+---------------------+-----------------+---------------------+---------------------+---------------------------+

Here I get all entities that are referenced in the uspGetClient stored procedure.

Here’s the actual definition for uspGetClient:

CREATE PROCEDURE [dbo].[uspGetClient] @ClientCode clientcode AS
SELECT 
    FirstName,
    LastName
FROM [dbo].[Client]
WHERE ClientCode = @ClientCode;

So we can see that it selects data from a table called Client, and it accepts an argument called @ClientCode with a (user-defined alias) data type of clientcode.

Example 3 – Find Entities on which an Entity Depends

You can also switch it around and get the objects on which a given entity depends. To do that, use referenced_id (instead of referencing_id) when selecting from the view.

Example:

SELECT
    OBJECT_NAME(referencing_id) AS [Referencing Entity],
    OBJECT_NAME(referencing_minor_id) AS [Referencing Minor Entity],
    referencing_class_desc AS [Class],
    COL_NAME(referenced_id, referenced_minor_id) AS [Column]
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('Client');

Result:

+----------------------+----------------------------+------------------+------------+
| Referencing Entity   | Referencing Minor Entity   | Class            | Column     |
|----------------------+----------------------------+------------------+------------|
| uspGetClient         | NULL                       | OBJECT_OR_COLUMN | NULL       |
| uspGetOrdersByClient | NULL                       | OBJECT_OR_COLUMN | NULL       |
| chkClientCode        | NULL                       | OBJECT_OR_COLUMN | ClientCode |
+----------------------+----------------------------+------------------+------------+

In this example I wanted to see which entities depend on the Client table (i.e. which entities reference that table in their SQL code).

You’ll notice that I selected different columns too. This is because I’m looking for information about the referencing entity, not the referenced entity like in the previous example.

Example 4 – Retrieve More Information

You can join this view with other views and/or tables to return more information.

For example, you can join it with sys.objects to get the referencing object’s type:

SELECT OBJECT_NAME(referencing_id) AS [Referencing Entity],   
    o.type_desc AS [Type],   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS [Column],
    referenced_entity_name AS [Referenced Entity],   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS [Column]
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'Client');

Result:

+----------------------+----------------------+----------+---------------------+------------+
| Referencing Entity   | Type                 | Column   | Referenced Entity   | Column     |
|----------------------+----------------------+----------+---------------------+------------|
| uspGetClient         | SQL_STORED_PROCEDURE | (n/a)    | Client              | (n/a)      |
| uspGetOrdersByClient | SQL_STORED_PROCEDURE | (n/a)    | Client              | (n/a)      |
| chkClientCode        | CHECK_CONSTRAINT     | (n/a)    | Client              | ClientCode |
+----------------------+----------------------+----------+---------------------+------------+

In this example I also added the COALESCE() function to return (n/a) whenever referencing_minor_id is NULL. This function is one of several ways you can replace a NULL value with a string in SQL Server.

Example 5 – Cross-Database & Cross-Server Entities

As mentioned, sql_expression_dependencies also works on cross-database and cross-server entities. However, in this case, entity names are reported but entity IDs are not resolved.

This example uses exactly the same code as with Example 2, except this time it’s for a different entity. This time, I want to find entities that depend on uspGetAlbumsByArtist:

SELECT
    referenced_server_name AS [Referenced Server],
    referenced_database_name AS [Referenced DB],
    referenced_schema_name AS [Referenced Schema],
    referenced_entity_name AS [Referenced Entity],
    referenced_class_desc AS [Referenced Entity Class] 
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('uspGetAlbumsByArtist');

Result:

+---------------------+-----------------+---------------------+---------------------+---------------------------+
| Referenced Server   | Referenced DB   | Referenced Schema   | Referenced Entity   | Referenced Entity Class   |
|---------------------+-----------------+---------------------+---------------------+---------------------------|
| Homer               | Music           | dbo                 | Albums              | OBJECT_OR_COLUMN          |
+---------------------+-----------------+---------------------+---------------------+---------------------------+

In this example, the referenced server and referenced database have a value (instead of being NULL like in the earlier example). That’s because the uspGetAlbumsByArtist stored procedure uses a four-part name to reference the entity on a linked server (the stored procedure from the earlier example didn’t use a four part name, and it didn’t use a three-part name to specify the DB either).

In this example, Homer is the name of the linked server, and Music is the database that the stored procedure queries.

We can see this in uspGetAlbumsByArtist‘s definition:

CREATE PROCEDURE [dbo].[uspGetAlbumsByArtist] @ArtistId int AS
SELECT AlbumName
FROM [Homer].[Music].[dbo].[Albums]
WHERE ArtistId = @ArtistId;

Official Documentation

For more detailed information and examples, see sys.sql_expression_dependencies on the Microsoft website.

Here’s another Microsoft article which includes instructions for getting dependencies via SSMS.

Database.Guide

Find Dependencies in SQL Server: sql_expression_dependencies

Example 1 – All Columns Returned

Example 2 – Find Entities that Depend on an Entity

Example 3 – Find Entities on which an Entity Depends

Example 4 – Retrieve More Information

Example 5 – Cross-Database & Cross-Server Entities

Official Documentation