An invalid parameter is passed.
Returns an error when the specified referencing entity is a numbered stored procedure.
Returns error 2020 when column dependencies cannot be resolved. This error does not prevent the query from returning object-level dependencies.
This function can be executed in the context of the any database to return the entities that reference a server-level DDL trigger.
The following table lists the types of entities for which dependency information is created and maintained. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.
Entity type
Referencing entity
Referenced entity
* A table is tracked as a referencing entity only when it references a Transact-SQL module, user-defined type, or XML schema collection in the definition of a computed column, CHECK constraint, or DEFAULT constraint.
** Numbered stored procedures with an integer value greater than 1 are not tracked as either a referencing or referenced entity.
Permissions
Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW DEFINITION permission on the referencing entity. By default, SELECT permission is granted to public. Requires VIEW DEFINITION permission on the database or ALTER DATABASE DDL TRIGGER permission on the database when the referencing entity is a database-level DDL trigger. Requires VIEW ANY DEFINITION permission on the server when the referencing entity is a server-level DDL trigger.
Examples
A. Return entities that are referenced by a database-level DDL trigger
The following example returns the entities (tables and columns) that are referenced by the database-level DDL trigger ddlDatabaseTriggerLog.
USE AdventureWorks2022;
SELECT
referenced_schema_name,
referenced_entity_name,
referenced_minor_name,
referenced_minor_id,
referenced_class_desc
sys.dm_sql_referenced_entities (
'ddlDatabaseTriggerLog',
'DATABASE_DDL_TRIGGER')
B. Return entities that are referenced by an object
The following example returns the entities that are referenced by the user-defined function dbo.ufnGetContactInformation.
USE AdventureWorks2022;
SELECT
referenced_schema_name,
referenced_entity_name,
referenced_minor_name,
referenced_minor_id,
referenced_class_desc,
is_caller_dependent,
is_ambiguous
sys.dm_sql_referenced_entities (
'dbo.ufnGetContactInformation',
'OBJECT')
C. Return column dependencies
The following example creates the table Table1 with the computed column c defined as the sum of columns a and b. The sys.dm_sql_referenced_entities view is then called. The view returns two rows, one for each column defined in the computed column.
CREATE TABLE dbo.Table1 (a int, b int, c AS a + b);
SELECT
referenced_schema_name AS schema_name,
referenced_entity_name AS table_name,
referenced_minor_name AS referenced_column,
COALESCE(
COL_NAME(OBJECT_ID(N'dbo.Table1'),
referencing_minor_id),
'N/A') AS referencing_column_name
sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT')
-- Remove the table.
DROP TABLE dbo.Table1;
Here is the result set.
schema_name table_name referenced_column referencing_column
----------- ---------- ----------------- ------------------
dbo Table1 a c
dbo Table1 b c
D. Returning non-schema-bound column dependencies
The following example drops Table1 and creates Table2 and stored procedure Proc1. The procedure references Table2 and the nonexistent table Table1. The view sys.dm_sql_referenced_entities is run with the stored procedure specified as the referencing entity. The result set shows one row for Table1 and 3 rows for Table2. Because Table1 does not exist, the column dependencies cannot be resolved and error 2020 is returned. The is_all_columns_found column returns 0 for Table1 indicating that there were columns that could not be discovered.
DROP TABLE IF EXISTS dbo.Table1;
CREATE TABLE dbo.Table2 (c1 int, c2 int);
CREATE PROCEDURE dbo.Proc1 AS
SELECT a, b, c FROM Table1;
SELECT c1, c2 FROM Table2;
SELECT
referenced_id,
referenced_entity_name AS table_name,
referenced_minor_name AS referenced_column_name,
is_all_columns_found
sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
Here is the result set.
referenced_id table_name referenced_column_name is_all_columns_found
------------- ------------ ----------------------- --------------------
935674381 Table2 NULL 1
935674381 Table2 C1 1
935674381 Table2 C2 1
NULL Table1 NULL 0
Msg 2020, Level 16, State 1, Line 1
The dependencies reported for entity "dbo.Proc1" 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.
E. Demonstrating dynamic dependency maintenance
This Example E assumes that Example D has been run. Example E shows that dependencies are maintained dynamically. The example does the following things:
Re-creates Table1, which was dropped in Example D.
Run Then sys.dm_sql_referenced_entities is run again with the stored procedure specified as the referencing entity.
The result set shows that both tables, and their respective columns defined in the stored procedure, are returned. In addition, the is_all_columns_found column returns a 1 for all objects and columns.
CREATE TABLE Table1 (a int, b int, c AS a + b);
SELECT
referenced_id,
referenced_entity_name AS table_name,
referenced_minor_name AS column_name,
is_all_columns_found
sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
DROP TABLE Table1, Table2;
DROP PROC Proc1;
Here is the result set.
referenced_id table_name referenced_column_name is_all_columns_found
------------- ------------ ----------------------- --------------------
935674381 Table2 NULL 1
935674381 Table2 c1 1
935674381 Table2 c2 1
967674495 Table1 NULL 1
967674495 Table1 a 1
967674495 Table1 b 1
967674495 Table1 c 1
F. Returning object or column usage
The following example returns the objects and column dependencies of the stored procedure HumanResources.uspUpdateEmployeePersonalInfo. This procedure updates the columns NationalIDNumber, BirthDate,``MaritalStatus, and Gender of the Employee table based on a specified BusinessEntityID value. Another stored procedure, upsLogError is defined in a TRY...CATCH block to capture any execution errors. The is_selected, is_updated, and is_select_all columns return information about how these objects and columns are used within the referencing object. The table and columns that are modified are indicated by a 1 in the is_updated column. The BusinessEntityID column is only selected and the stored procedure uspLogError is neither selected nor modified.
USE AdventureWorks2022;
SELECT
referenced_entity_name AS table_name,
referenced_minor_name AS column_name,
is_selected, is_updated, is_select_all
sys.dm_sql_referenced_entities(
'HumanResources.uspUpdateEmployeePersonalInfo',
'OBJECT')
Here is the result set.
table_name column_name is_selected is_updated is_select_all
------------- ------------------- ----------- ---------- -------------
uspLogError NULL 0 0 0
Employee NULL 0 1 0
Employee BusinessEntityID 1 0 0
Employee NationalIDNumber 0 1 0
Employee BirthDate 0 1 0
Employee MaritalStatus 0 1 0
Employee Gender 0 1 0
See Also
sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for
This product
