sys.dm_sql_referenced_entities (Transact-SQL)

Syntax

sys.dm_sql_referenced_entities (
    ' [ schema_name. ] referencing_entity_name ' , ' <referencing_class> ' )

<referencing_class> ::=
{
    OBJECT
  | DATABASE_DDL_TRIGGER
  | SERVER_DDL_TRIGGER
}

Arguments

• [ schema_name. ] referencing_entity_name
  Is the name of the referencing entity. schema_name is required when the referencing class is OBJECT.

  schema_name.referencing_entity_name is nvarchar(517).

• <referencing_class> ::= { OBJECT | DATABASE_DDL_TRIGGER   | SERVER_DDL_TRIGGER }
  Is the class of the specified referencing entity. Only one class can be specified per statement.

  <referencing_class> is nvarchar(60).

Table Returned

Exceptions

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 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. For more information, see Troubleshooting SQL Dependencies.

Remarks

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.

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

For more information, see Understanding SQL Dependencies.

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. Returning entities that are referenced by a database-level DDL trigger

USE AdventureWorks2008R2;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, 
    referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');
GO

B. Returning entities that are referenced by an object

USE AdventureWorks2008R2;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, 
    referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');
GO

C. Returning column dependencies

USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Table1 (a int, b int, c AS a + b);
GO
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
FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT');
GO
-- Remove the table.
DROP TABLE dbo.Table1;
GO

D. Returning non-schema-bound column dependencies

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.Table1', 'U' ) IS NOT NULL 
    DROP TABLE dbo.Table1;
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
    SELECT a, b, c FROM Table1;
    SELECT c1, c2 FROM Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS referenced_column_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO

E. Demonstrating dynamic dependency maintenance

USE AdventureWorks2008R2;
GO
CREATE TABLE Table1 (a int, b int, c AS a + b);
GO 
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name as column_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO
DROP TABLE Table1, Table2;
DROP PROC Proc1;
GO

See Also

Tasks

Reference

Concepts