sys.dm_sql_referencing_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns one row for each entity in the current database that references another user-defined entity by name. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, if a user-defined type (UDT) is specified as the referenced entity, this function returns each user-defined entity that reference that type by name in its definition. The function does not return entities in other databases that may reference the specified entity. This function must be executed in the context of the master database to return a server-level DDL trigger as a referencing entity.

You can use this dynamic management function to report on the following types of entities in the current database that reference the specified entity:

  • Schema-bound or non-schema-bound entities

  • Database-level DDL triggers

  • Server-level DDL triggers

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12.

Topic link icon Transact-SQL Syntax Conventions

  
sys.dm_sql_referencing_entities (  
    ' schema_name.referenced_entity_name ' , ' <referenced_class> ' )  
  
<referenced_class> ::=  
{  
    OBJECT  
  | TYPE  
  | XML_SCHEMA_COLLECTION  
  | PARTITION_FUNCTION  
}  

schema_name.referenced_entity_name
Is the name of the referenced entity.

schema_name is required except when the referenced class is PARTITION_FUNCTION.

schema_name.referenced_entity_name is nvarchar(517).

<referenced_class> ::= { OBJECT | TYPE | XML_SCHEMA_COLLECTION | PARTITION_FUNCTION }
Is the class of the referenced entity. Only one class can be specified per statement.

<referenced_class> is nvarchar(60).

Column nameData typeDescription
referencing_schema_namesysnameSchema in which the referencing entity belongs. Is nullable.

NULL for database-level and server-level DDL triggers.
referencing_entity_namesysnameName of the referencing entity. Is not nullable.
referencing_idintID of the referencing entity. Is not nullable.
referencing_classtinyintClass of the referencing entity. Is not nullable.

1 = Object

12 = Database-level DDL trigger

13 = Server-level DDL trigger
referencing_class_descnvarchar(60)Description of class of referencing entity.

OBJECT

DATABASE_DDL_TRIGGER

SERVER_DDL_TRIGGER
is_caller_dependentbitIndicates the resolution of the referenced entity ID occurs at run time because it depends on the schema of the caller.

1 = The referencing entity has the potential to reference the entity; however, resolution of the referenced entity ID is caller dependent and cannot be determined. This occurs only for non-schema-bound references to a stored procedure, extended stored procedure, or user-defined function called in an EXECUTE statement.

0 = Referenced entity is not caller dependent.

Returns an empty result set under any of the following conditions:

  • A system object is specified.

  • The specified entity does not exist in the current database.

  • The specified entity does not reference any entities.

  • An invalid parameter is passed.

Returns an error when the specified referenced entity is a numbered stored procedure.

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 typeReferencing entityReferenced entity
TableYes*Yes
ViewYesYes
Transact-SQL stored procedure**YesYes
CLR stored procedureNoYes
Transact-SQL user-defined functionYesYes
CLR user-defined functionNoYes
CLR trigger (DML and DDL)NoNo
Transact-SQL DML triggerYesNo
Transact-SQL database-level DDL triggerYesNo
Transact-SQL server-level DDL triggerYesNo
Extended stored proceduresNoYes
QueueNoYes
SynonymNoYes
Type (alias and CLR user-defined type)NoYes
XML schema collectionNoYes
Partition functionNoYes

* 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.

SQL Server 2008 – SQL Server 2012

  • Requires CONTROL permission on the referenced object. When the referenced entity is a partition function, CONTROL permission on the database is required.

  • Requires SELECT permission on sys.dm_sql_referencing_entities. By default, SELECT permission is granted to public.

SQL Server 2014 - SQL Server 2016

  • Requires no permissions on the referenced object. Partial results can be returned if the user has VIEW DEFINITION on only some of the referencing entities.

  • Requires VIEW DEFINITION on the object when the referencing entity is an object.

  • Requires VIEW DEFINITION on the database when the referencing entity is a database-level DDL trigger.

  • Requires VIEW ANY DEFINITION on the server when the referencing entity is a server-level DDL trigger.

A. Returning the entities that refer to a given entity

The following example returns the entities in the current database that refer to the specified table.

USE AdventureWorks2012;  
GO  
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent  
FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');  
GO  

B. Returning the entities that refer to a given type

The following example returns the entities that reference the alias type dbo.Flag. The result set shows that two stored procedures use this type. The dbo.Flag type is also used in the definition of several columns in the HumanResources.Employee table; however, because the type is not in the definition of a computed column, CHECK constraint, or DEFAULT constraint in the table, no rows are returned for the HumanResources.Employee table.

USE AdventureWorks2012;  
GO  
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent  
FROM sys.dm_sql_referencing_entities ('dbo.Flag', 'TYPE');  
GO  

Here is the result set.

referencing_schema_name referencing_entity_name referencing_id referencing_class_desc is_caller_dependent

----------------------- ------------------------- ------------- ---------------------- -------------------

HumanResources uspUpdateEmployeeHireInfo 1803153469 OBJECT_OR_COLUMN 0

HumanResources uspUpdateEmployeeLogin 1819153526 OBJECT_OR_COLUMN 0

(2 row(s) affected)

sys.dm_sql_referenced_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft