In SQL Server, you can use the sys.dm_sql_referencing_entities() system dynamic management function to get a list of all entities in the current database that reference another user-defined entity by name.
In other words, it returns a list of entities that depend on the given entity.
Specifically, it reports on the following entity types in the current database that reference the specified entity:
- Schema-bound or non-schema-bound entities
- Database-level DDL triggers
- Server-level DDL triggers
Syntax
The syntax goes like this:
sys.dm_sql_referencing_entities (
' schema_name.referenced_entity_name ' , ' ' )
::=
{
OBJECT
| TYPE
| XML_SCHEMA_COLLECTION
| PARTITION_FUNCTION
}Example 1 – Basic Example
Here’s a basic example of usage:
USE WideWorldImporters;
SELECT *
FROM sys.dm_sql_referencing_entities (
'Application.Cities',
'OBJECT');Result:
+---------------------------+---------------------------+------------------+---------------------+--------------------------+-----------------------+
| referencing_schema_name | referencing_entity_name | referencing_id | referencing_class | referencing_class_desc | is_caller_dependent |
|---------------------------+---------------------------+------------------+---------------------+--------------------------+-----------------------|
| Application | DetermineCustomerAccess | 1051150790 | 1 | OBJECT_OR_COLUMN | 0 |
| Integration | GetCityUpdates | 1758629308 | 1 | OBJECT_OR_COLUMN | 0 |
| Website | Customers | 1694629080 | 1 | OBJECT_OR_COLUMN | 0 |
| Website | SearchForCustomers | 942626401 | 1 | OBJECT_OR_COLUMN | 0 |
| Website | SearchForSuppliers | 926626344 | 1 | OBJECT_OR_COLUMN | 0 |
| Website | Suppliers | 1678629023 | 1 | OBJECT_OR_COLUMN | 0 |
+---------------------------+---------------------------+------------------+---------------------+--------------------------+-----------------------+
In this example, there are six entities that reference ‘Application.Cities’ in the ‘WideWorldImporters’ database.
Microsoft specifically recommends against using the asterisk (*) to select all columns from dynamic management views and functions (of which sys.dm_sql_referencing_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.
Therefore, the previous code should be rewritten to this:
Example:
USE WideWorldImporters;
SELECT
referencing_schema_name,
referencing_entity_name,
referencing_id,
referencing_class,
referencing_class_desc,
is_caller_dependent
FROM sys.dm_sql_referencing_entities (
'Application.Cities',
'OBJECT');Of course, you can always specify less columns if you want.
Example 2 – Get the Referencing Entity Type
The above example is all good and well, but it doesn’t tell us the referencing entity’s type. In other words, we can’t see whether it’s a view, a stored procedure, etc.
You can get this information by joining sys.dm_sql_referencing_entities() with sys.objects.
So we could modify the previous example like this:
SELECT
o.type_desc 'Type',
re.referencing_schema_name 'Schema',
re.referencing_entity_name 'Name'
FROM sys.dm_sql_referencing_entities (
'Application.Cities',
'OBJECT') re
INNER JOIN sys.objects o
ON re.referencing_id = o.object_id
ORDER BY 'Type' ASC;Result:
+----------------------------------+-------------+-------------------------+
| Type | Schema | Name |
|----------------------------------+-------------+-------------------------|
| SQL_INLINE_TABLE_VALUED_FUNCTION | Application | DetermineCustomerAccess |
| SQL_STORED_PROCEDURE | Integration | GetCityUpdates |
| SQL_STORED_PROCEDURE | Website | SearchForCustomers |
| SQL_STORED_PROCEDURE | Website | SearchForSuppliers |
| VIEW | Website | Suppliers |
| VIEW | Website | Customers |
+----------------------------------+-------------+-------------------------+
Example 3 – User-Defined Types
Here’s an example of using sys.dm_sql_referencing_entities() to return entities that reference a given user-defined alias type.
For this example, I created a user-defined alias called clientcode. I then used it in two columns (in two different tables), and I also created a stored procedure that references the type by name (it accepts an argument called @ClientCode which is of the clientcode type).
To return a user-defined type, use TYPE as the second argument.
Example:
USE Test;
SELECT
referencing_entity_name
FROM sys.dm_sql_referencing_entities (
'dbo.clientcode',
'TYPE');Result:
+---------------------------+
| referencing_entity_name |
|---------------------------|
| uspGetClient |
| uspGetOrdersByClient |
+---------------------------+
I can tell by the names that both of these are stored procedures (they’re both prefixed with usp, which is a common convention when creating user-defined stored procedures), but we can confirm this by checking the sys.objects system catalog view once again:
SELECT
o.type_desc 'Type',
re.referencing_schema_name 'Schema',
re.referencing_entity_name 'Name'
FROM sys.dm_sql_referencing_entities (
'dbo.clientcode',
'TYPE') re
INNER JOIN sys.objects o
ON re.referencing_id = o.object_id
ORDER BY 'Type' ASC;Result:
+----------------------+----------+----------------------+
| Type | Schema | Name |
|----------------------+----------+----------------------|
| SQL_STORED_PROCEDURE | dbo | uspGetClient |
| SQL_STORED_PROCEDURE | dbo | uspGetOrdersByClient |
+----------------------+----------+----------------------+
Note that this alias type is used in the columns of two tables in this database. However, these don’t appear in our list of dependencies because the user-defined type is not in the definition of a computed column, CHECK constraint, or DEFAULT constraint in the table.
Also, one of the tables references the other table via a foreign key constraint on the column that uses the dbo.clientcode user-defined type, but this also doesn’t appear in our list.
Official Documentation
For more detailed information, see sys.dm_sql_referencing_entities on the Microsoft website.