Find Referenced Entities in SQL Server: sys.dm_sql_referenced

In SQL Server, you can use the sys.dm_sql_referenced_entities() system dynamic management function to get a list of all user-defined entities that are referenced by name, in the definition of a given entity.

In other words, it returns a list of all user-defined entities that a specific entity depends on.

Specifically, it reports on the following entity types referenced by the specified referencing entity:

Schema-bound entities
Non-schema-bound entities
Cross-database and cross-server entities
Column-level dependencies on schema-bound and non-schema-bound entities
User-defined types (alias and CLR UDT)
XML schema collections
Partition functions

Syntax

The syntax goes like this:

sys.dm_sql_referenced_entities (  
    ' [ schema_name. ] referencing_entity_name ' ,
    '  ' )  
  
 ::=  
{  
    OBJECT  
  | DATABASE_DDL_TRIGGER  
  | SERVER_DDL_TRIGGER  
}

Example 1 – Basic Example

Here’s an example of usage:

USE Test;
SELECT 
  referenced_schema_name AS [Schema],
  referenced_entity_name AS Entity,
  referenced_minor_name AS Minor,
  referenced_class_desc AS Class,
  is_select_all,
  is_all_columns_found
FROM sys.dm_sql_referenced_entities (
    'dbo.uspGetClient', 
    'OBJECT');

Result:

+----------+------------+------------+------------------+-----------------+------------------------+
| Schema   | Entity     | Minor      | Class            | is_select_all   | is_all_columns_found   |
|----------+------------+------------+------------------+-----------------+------------------------|
| dbo      | Client     | NULL       | OBJECT_OR_COLUMN | 1               | 1                      |
| dbo      | Client     | ClientCode | OBJECT_OR_COLUMN | 1               | 1                      |
| dbo      | Client     | FirstName  | OBJECT_OR_COLUMN | 1               | 1                      |
| dbo      | Client     | LastName   | OBJECT_OR_COLUMN | 1               | 1                      |
| NULL     | clientcode | NULL       | TYPE             | 0               | 0                      |
+----------+------------+------------+------------------+-----------------+------------------------+

Here I get all the entities that are referenced in the dbo.uspGetClient stored procedure. In this case there are five entities.

The first one is the table called “Client”. The next three are all columns within that table. The last one is a user-defined alias data type called “clientcode”.

We can also see that the first four are used in a select statement that uses the asterisk (*) wildcard to select all columns (because their is_select_all is set to 1).

Here’s the actual definition used to create the stored procedure that we’re analysing:

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

Yes, it’s a very simple stored procedure, but it’s ideal for our purposes. We can see all of the referenced entities as returned by sys.dm_sql_referenced_entities().

We can also see that the procedure consists of a single SELECT query that uses the asterisk wildcard to select all columns.

Example 2 – Remove the “Select All” (`*`)

Let’s alter the stored procedure so that it doesn’t use the asterisk wildcard to select all columns.

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

So it now explicitly returns the “FirstName” and “LastName” columns. No wildcards to be found.

Now run sys.dm_sql_referenced_entities() again:

USE Test;
SELECT 
  referenced_schema_name AS [Schema],
  referenced_entity_name AS Entity,
  referenced_minor_name AS Minor,
  referenced_class_desc AS Class,
  is_select_all,
  is_all_columns_found
FROM sys.dm_sql_referenced_entities (
    'dbo.uspGetClient', 
    'OBJECT');

Result:

+----------+------------+------------+------------------+-----------------+------------------------+
| Schema   | Entity     | Minor      | Class            | is_select_all   | is_all_columns_found   |
|----------+------------+------------+------------------+-----------------+------------------------|
| dbo      | Client     | NULL       | OBJECT_OR_COLUMN | 0               | 1                      |
| dbo      | Client     | ClientCode | OBJECT_OR_COLUMN | 0               | 1                      |
| dbo      | Client     | FirstName  | OBJECT_OR_COLUMN | 0               | 1                      |
| dbo      | Client     | LastName   | OBJECT_OR_COLUMN | 0               | 1                      |
| NULL     | clientcode | NULL       | TYPE             | 0               | 0                      |
+----------+------------+------------+------------------+-----------------+------------------------+

This time, the is_select_all column shows 0 on all rows.

Example 3 – Referencing a Non-Existent Entity

What if your entity references a non-existent entity?

For example, what if your colleague drops a column that’s actually being referenced by a stored procedure, and then you run sys.dm_sql_referenced_entities() against that stored procedure?

Let’s find out.

ALTER TABLE [dbo].[Client] 
DROP COLUMN LastName;

I just dropped the LastName column from my table.

Now run sys.dm_sql_referenced_entities() again:

SELECT 
  referenced_schema_name AS [Schema],
  referenced_entity_name AS Entity,
  referenced_minor_name AS Minor,
  referenced_class_desc AS Class,
  is_select_all,
  is_all_columns_found
FROM sys.dm_sql_referenced_entities (
    'dbo.uspGetClient', 
    'OBJECT');

Result:

Msg 207, Level 16, State 1, Procedure uspGetClient, Line 4
Invalid column name 'LastName'.
Msg 2020, Level 16, State 1, Line 3
The dependencies reported for entity "dbo.uspGetClient" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

Example 4 – Drop the Whole Table

Let’s find out what happens if we drop the whole table.

DROP TABLE Client;

The table has been dropped.

Run sys.dm_sql_referenced_entities():

SELECT 
  referenced_schema_name AS [Schema],
  referenced_entity_name AS Entity,
  referenced_minor_name AS Minor,
  referenced_class_desc AS Class,
  is_select_all,
  is_all_columns_found
FROM sys.dm_sql_referenced_entities (
    'dbo.uspGetClient', 
    'OBJECT');

Result:

Msg 2020, Level 16, State 1, Line 2
The dependencies reported for entity "dbo.uspGetClient" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

Example 5 – Return All Columns

Microsoft specifically recommends against using the asterisk (*) to select all columns from dynamic management views and functions (of which sys.dm_sql_referenced_entities() is one). This is because their schemas and the data they return may change in future releases of SQL Server. This could result in columns being added to the end of the column list in future releases, which could mess up your application if you’re relying on the asterisk to select all columns.

That said, here’s an example that does just that: uses the asterisk (*) to select all columns from sys.dm_sql_referenced_entities(). I’m only doing this to show you what columns are actually returned from this function (at least in SQL Server 2019).

SELECT *
FROM sys.dm_sql_referenced_entities (
    'dbo.uspGetClient', 
    'OBJECT');

Result (using vertical output):

-[ RECORD 1 ]-------------------------
referencing_minor_id     | 0
referenced_server_name   | NULL
referenced_database_name | NULL
referenced_schema_name   | dbo
referenced_entity_name   | Client
referenced_minor_name    | NULL
referenced_id            | 434100587
referenced_minor_id      | 0
referenced_class         | 1
referenced_class_desc    | OBJECT_OR_COLUMN
is_caller_dependent      | 0
is_ambiguous             | 0
is_selected              | 1
is_updated               | 0
is_select_all            | 0
is_all_columns_found     | 1
is_insert_all            | 0
is_incomplete            | 0
-[ RECORD 2 ]-------------------------
referencing_minor_id     | 0
referenced_server_name   | NULL
referenced_database_name | NULL
referenced_schema_name   | dbo
referenced_entity_name   | Client
referenced_minor_name    | ClientCode
referenced_id            | 434100587
referenced_minor_id      | 1
referenced_class         | 1
referenced_class_desc    | OBJECT_OR_COLUMN
is_caller_dependent      | 0
is_ambiguous             | 0
is_selected              | 1
is_updated               | 0
is_select_all            | 0
is_all_columns_found     | 1
is_insert_all            | 0
is_incomplete            | 0
-[ RECORD 3 ]-------------------------
referencing_minor_id     | 0
referenced_server_name   | NULL
referenced_database_name | NULL
referenced_schema_name   | dbo
referenced_entity_name   | Client
referenced_minor_name    | FirstName
referenced_id            | 434100587
referenced_minor_id      | 2
referenced_class         | 1
referenced_class_desc    | OBJECT_OR_COLUMN
is_caller_dependent      | 0
is_ambiguous             | 0
is_selected              | 1
is_updated               | 0
is_select_all            | 0
is_all_columns_found     | 1
is_insert_all            | 0
is_incomplete            | 0
-[ RECORD 4 ]-------------------------
referencing_minor_id     | 0
referenced_server_name   | NULL
referenced_database_name | NULL
referenced_schema_name   | dbo
referenced_entity_name   | Client
referenced_minor_name    | LastName
referenced_id            | 434100587
referenced_minor_id      | 3
referenced_class         | 1
referenced_class_desc    | OBJECT_OR_COLUMN
is_caller_dependent      | 0
is_ambiguous             | 0
is_selected              | 1
is_updated               | 0
is_select_all            | 0
is_all_columns_found     | 1
is_insert_all            | 0
is_incomplete            | 0
-[ RECORD 5 ]-------------------------
referencing_minor_id     | 0
referenced_server_name   | NULL
referenced_database_name | NULL
referenced_schema_name   | NULL
referenced_entity_name   | clientcode
referenced_minor_name    | NULL
referenced_id            | 257
referenced_minor_id      | 0
referenced_class         | 6
referenced_class_desc    | TYPE
is_caller_dependent      | 0
is_ambiguous             | 0
is_selected              | 0
is_updated               | 0
is_select_all            | 0
is_all_columns_found     | 0
is_insert_all            | 0
is_incomplete            | 0
(5 rows affected)

Official Documentation

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

Database.Guide

Find Referenced Entities in SQL Server: sys.dm_sql_referenced_entities

Syntax

Example 1 – Basic Example

Example 2 – Remove the “Select All” (`*`)

Example 3 – Referencing a Non-Existent Entity

Example 4 – Drop the Whole Table

Example 5 – Return All Columns

Official Documentation

Syntax

Example 1 – Basic Example

Example 2 – Remove the “Select All” (*)

Example 3 – Referencing a Non-Existent Entity

Example 4 – Drop the Whole Table

Example 5 – Return All Columns

Official Documentation

Example 2 – Remove the “Select All” (`*`)