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

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

个中的指定引用实体的定义中按名称引用的每个用户定义实体返回一行SQL ServerSQL ServerReturns one row for each user-defined entity that is referenced by name in the definition of the specified referencing entity in SQL ServerSQL Server. 一个用户定义的实体,调用时创建两个实体之间的依赖关系被引用的实体,在另一个用户定义的实体,名为的持久化 SQL 表达式中按名称将显示引用实体.A dependency between two entities is created when one user-defined entity, called the referenced entity, appears by name in a persisted SQL expression of another user-defined entity, called the referencing entity. 例如,如果存储过程是指定的引用实体,则此函数会返回该存储过程中引用的所有用户定义实体,例如表、视图、用户定义类型 (UDT) 或其他存储过程。For example, if a stored procedure is the specified referencing entity, this function returns all user-defined entities that are referenced in the stored procedure such as tables, views, user-defined types (UDTs), or other stored procedures.

可以使用此动态管理函数报告指定的引用实体引用的以下类型的实体:You can use this dynamic management function to report on the following types of entities 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

  • 用户定义类型(别名和 CLR UDT)User-defined types (alias and CLR UDT)

  • XML 架构集合XML schema collections

  • 分区函数Partition functions

语法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[ schema_name. ] referencing_entity_name] referencing_entity_name
是引用实体的名称。Is the name of the referencing entity. schema_name引用类为 OBJECT 时是必需的。schema_name is required when the referencing class is OBJECT.

schema_name.referencing_entity_namenvarchar(517)schema_name.referencing_entity_name is nvarchar(517).

< Referencing_class > :: = {对象 |DATABASE_DDL_TRIGGER |SERVER_DDL_TRIGGER}<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 >nvarchar(60)<referencing_class> is nvarchar(60).

返回的表Table Returned

列名Column name 数据类型Data type 描述Description
referencing_minor_idreferencing_minor_id intint 引用实体为列时的列 ID;否则为 0。Column ID when the referencing entity is a column; otherwise 0. 不可为 null。Is not nullable.
referenced_server_namereferenced_server_name sysnamesysname 被引用的实体的服务器的名称。Name of the server of the referenced entity.

此列是为通过指定由四个部分组成的有效名称所生成的跨服务器依赖关系填充的。This column is populated for cross-server dependencies that are made by specifying a valid four-part name. 有关多部分名称的信息,请参阅TRANSACT-SQL 语法约定(TRANSACT-SQL)For information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL).

对于非绑定到架构的依赖关系,如果引用实体时没有指定由四个部分组成的名称,此列为 NULL。NULL for non-schema-bound dependencies for which the entity was referenced without specifying a four-part name.

对于绑定到架构的实体为 NULL,因为它们必须在同一数据库中,因此可以仅使用定义两个部分 (schema.object) 名称。NULL for schema-bound entities because they must be in the same database and therefore can only be defined using a two-part (schema.object) name.
referenced_database_namereferenced_database_name sysnamesysname 被引用的实体的数据库的名称。Name of the database of the referenced entity.

此列是为通过指定由三个部分或四个部分组成的有效名称生成的跨数据库或跨服务器引用填充的。This column is populated for cross-database or cross-server references that are made by specifying a valid three-part or four-part name.

对于非绑定到架构的引用,当使用由一个部分或两个部分组成的名称指定时,此列为 NULL。NULL for non-schema-bound references when specified using a one-part or two-part name.

对于绑定到架构的实体为 NULL,因为它们必须在同一数据库中,因此可以仅使用定义两个部分 (schema.object) 名称。NULL for schema-bound entities because they must be in the same database and therefore can only be defined using a two-part (schema.object) name.
referenced_schema_namereferenced_schema_name sysnamesysname 被引用的实体所属的架构。Schema in which the referenced entity belongs.

对于非绑定到架构的引用,如果实体被引用时没有指定架构名称,此列为 NULL。NULL for non-schema-bound references in which the entity was referenced without specifying the schema name.

对于绑定到架构的引用,此列永远不会为 NULL。Never NULL for schema-bound references.
referenced_entity_namereferenced_entity_name sysnamesysname 被引用的实体的名称。Name of the referenced entity. 不可为 null。Is not nullable.
referenced_minor_namereferenced_minor_name sysnamesysname 引用的实体是列时是列名;否则是 NULL。Column name when the referenced entity is a column; otherwise NULL. 例如,referenced_minor_name 在列出所引用的实体自身的行中是 NULL。For example, referenced_minor_name is NULL in the row that lists the referenced entity itself.

当列在引用实体中按名称标识时,或者当 SELECT * 语句中使用了父实体时,被引用的实体为列。A referenced entity is a column when a column is identified by name in the referencing entity, or when the parent entity is used in a SELECT * statement.
referenced_idreferenced_id intint 被引用的实体的 ID。ID of the referenced entity. 当 referenced_minor_id 不为 0 时,referenced_id 是该列被定义时所在的实体。When referenced_minor_id is not 0, referenced_id is the entity in which the column is defined.

对于跨服务器的引用,此列始终为 NULL。Always NULL for cross-server references.

对于跨数据库的引用,当由于数据库脱机或实体无法绑定而无法确定 ID 时,此列为 NULL。NULL for cross-database references when the ID cannot be determined because the database is offline or the entity cannot be bound.

对于数据库内的引用,如果无法确定 ID,则为 NULL。NULL for references within the database if the ID cannot be determined. 对于非绑定到架构的引用,在数据库中或依赖于调用方的名称解析时,被引用的实体不存在时,无法解析 ID。For non-schema-bound references, the ID cannot be resolved when the referenced entity does not exist in the database or when the name resolution is caller dependent. 在后一种情况下,is_caller_dependent 设置为 1。In the latter case, is_caller_dependent is set to 1.

对于绑定到架构的引用,此列永远不会为 NULL。Never NULL for schema-bound references.
referenced_minor_idreferenced_minor_id intint 引用的实体是列时是列 ID;否则是 0。Column ID when the referenced entity is a column; otherwise, 0. 例如,referenced_minor_is 在列出所引用的实体本身的行中是 0。For example, referenced_minor_is is 0 in the row that lists the referenced entity itself.

对于非绑定到架构的引用,只有当可以绑定所有被引用的实体时才报告列依赖关系。For non-schema-bound references, column dependencies are reported only when all referenced entities can be bound. 如果无法绑定任何被引用的实体,则不报告列级依赖关系,并且 referenced_minor_id 是 0。If any referenced entity cannot be bound, no column-level dependencies are reported and referenced_minor_id is 0. 请参见示例 D。See Example D.
referenced_classreferenced_class tinyinttinyint 被引用的实体的类。Class of the referenced entity.

1 = 对象或列1 = Object or column

6 = 类型6 = Type

10 = XML 架构集合10 = XML schema collection

21 = 分区函数21 = Partition function
referenced_class_descreferenced_class_desc nvarchar(60)nvarchar(60) 对被引用的实体的类的说明。Description of class of referenced entity.

OBJECT_OR_COLUMNOBJECT_OR_COLUMN

TYPETYPE

XML_SCHEMA_COLLECTIONXML_SCHEMA_COLLECTION

PARTITION_FUNCTIONPARTITION_FUNCTION
is_caller_dependentis_caller_dependent bitbit 指示被引用的实体的架构绑定发生于运行时,因此实体 ID 的解析依赖于调用方的架构。Indicates schema binding for the referenced entity occurs at run time; therefore, resolution of the entity ID depends on the schema of the caller. 当被引用的实体为存储过程、扩展存储过程或在 EXECUTE 语句中调用的用户定义函数时,将会出现这种情况。This occurs when the referenced entity is a stored procedure, extended stored procedure, or user-defined function called within an EXECUTE statement.

1 = 被引用的实体依赖调用方并在运行时解析。1 = The referenced entity is caller dependent and is resolved at run time. 在这种情况下,referenced_id 为 NULL。In this case, referenced_id is NULL.

0 = 被引用的实体 ID 不依赖调用方。0 = The referenced entity ID is not caller dependent. 对于绑定到架构的引用、显式指定架构名称的跨数据库和跨服务器的引用,始终为 0。Always 0 for schema-bound references and for cross-database and cross-server references that explicitly specify a schema name. 例如,对格式为 EXEC MyDatabase.MySchema.MyProc 的实体的引用不依赖于调用方。For example, a reference to an entity in the format EXEC MyDatabase.MySchema.MyProc is not caller dependent. 但是,格式为 EXEC MyDatabase..MyProc 的引用依赖调用方。However, a reference in the format EXEC MyDatabase..MyProc is caller dependent.
is_ambiguousis_ambiguous bitbit 指示引用不明确,可以在运行时对用户定义函数、 用户定义类型 (UDT) 或类型的列的 xquery 引用解析xmlIndicates the reference is ambiguous and can resolve at run time to a user-defined function, a user-defined type (UDT), or an xquery reference to a column of type xml. 例如,假定语句 SELECT Sales.GetOrder() FROM Sales.MySales 是在存储过程中定义的。For example, assume the statement SELECT Sales.GetOrder() FROM Sales.MySales is defined in a stored procedure. 在执行存储过程之前,并不知道 Sales.GetOrder()Sales 架构中的用户定义函数还是带有名为 Sales 的方法、类型为 UDT 且名为 GetOrder() 的列。Until the stored procedure is executed, it is not known whether Sales.GetOrder() is a user-defined function in the Sales schema or column named Sales of type UDT with a method named GetOrder().

1 = 引用的是用户定义函数还是使用用户定义类型 (UDT) 方法的列,这一点是不明确的。1 = Reference to a user-defined function or column user-defined type (UDT) method is ambiguous.

0 = 引用是明确的,或者在调用函数时可以成功绑定实体。0 = Reference is unambiguous or the entity can be successfully bound when the function is called.

对于绑定到架构的引用始终为 0。Always 0 for schema-bound references.
is_selectedis_selected bitbit 1 = 选中了对象或列。1 = The object or column is selected.
is_updatedis_updated bitbit 1 = 修改了对象或列。1 = The object or column is modified.
is_select_allis_select_all bitbit 1 = 对象用于 SELECT * 子句中(仅限对象级)。1 = The object is used in a SELECT * clause (object-level only).
is_all_columns_foundis_all_columns_found bitbit 1 = 可以找到对象的所有列依赖关系。1 = All column dependencies for the object could be found.

0 = 找不到对象的列依赖关系。0 = Column dependencies for the object could not be found.
is_insert_allis_insert_all bitbit 1 = 在 INSERT 语句没有列列表 (仅限对象级) 中使用的对象。1 = The object is used in an INSERT statement without a column list (object-level only).

SQL Server 2016 中已添加此列。This column was added in SQL Server 2016.
is_incompleteis_incomplete bitbit 1 = 对象或列已绑定错误并不完整。1 = The object or column has a binding error and is incomplete.

SQL Server 2016 SP2 中新增了此列。This column was added in SQL Server 2016 SP2.
     

ExceptionsExceptions

在满足以下任一条件时将返回空的结果集: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.

无法解析列依赖关系时,返回错误 2020。Returns error 2020 when column dependencies cannot be resolved. 此错误不会阻止查询返回对象级依赖关系。This error does not prevent the query from returning object-level dependencies.

备注Remarks

可以在任何数据库的上下文中执行此函数以返回引用服务器级 DDL 触发器的实体。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
Table 是*Yes* Yes
“查看”View Yes Yes
Transact-SQLTransact-SQL 存储过程**stored procedure** Yes Yes
CLR 存储过程CLR stored procedure No Yes
Transact-SQLTransact-SQL 用户定义函数user-defined function Yes Yes
CLR 用户定义函数CLR user-defined function No Yes
CLR 触发器(DML 和 DDL)CLR trigger (DML and DDL) No No
Transact-SQLTransact-SQL DML 触发器DML trigger Yes No
Transact-SQLTransact-SQL 数据库级 DDL 触发器database-level DDL trigger Yes No
Transact-SQLTransact-SQL 服务器级 DDL 触发器server-level DDL trigger Yes No
扩展存储过程Extended stored procedures No Yes
队列Queue No Yes
同义词Synonym No Yes
类型(别名和 CLR 用户定义类型)Type (alias and CLR user-defined type) No Yes
XML 架构集合XML schema collection No Yes
分区函数Partition function No Yes
     

* 仅当它引用时,跟踪表作为引用实体Transact-SQLTransact-SQL模块、 用户定义类型或 XML 架构集合定义中的计算的列、 CHECK 约束或 DEFAULT 约束。* A table is tracked as a referencing entity only when it references a Transact-SQLTransact-SQL module, user-defined type, or XML schema collection in the definition of a computed column, CHECK constraint, or DEFAULT constraint.

** 整数值大于 1 的带编号的存储过程将不会作为引用实体或被引用的实体进行跟踪。** Numbered stored procedures with an integer value greater than 1 are not tracked as either a referencing or referenced entity.

权限Permissions

要求对 sys.dm_sql_referenced_entities 拥有 SELECT 权限并对引用实体拥有 VIEW DEFINITION 权限。Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW DEFINITION permission on the referencing entity. 默认情况下,SELECT 权限授予 public。By default, SELECT permission is granted to public. 要求对数据库拥有 VIEW DEFINITION 权限或 ALTER DATABASE DDL TRIGGER 权限(当引用实体为数据库级 DDL 触发器时)。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. 当引用实体为服务器级 DDL 触发器时,要求对服务器拥有 VIEW ANY DEFINITION 权限。Requires VIEW ANY DEFINITION permission on the server when the referencing entity is a server-level DDL trigger.

示例Examples

A.A. 返回数据库级 DDL 触发器引用的实体Return entities that are referenced by a database-level DDL trigger

下面的示例返回数据库级 DDL 触发器 ddlDatabaseTriggerLog 引用的实体(表和列)。The following example returns the entities (tables and columns) that are referenced by the database-level DDL trigger ddlDatabaseTriggerLog.

USE AdventureWorks2012;  
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.B. 返回的对象引用的实体Return entities that are referenced by an object

下面的示例返回用户定义函数 dbo.ufnGetContactInformation 引用的实体。The following example returns the entities that are referenced by the user-defined function dbo.ufnGetContactInformation.

USE AdventureWorks2012;  
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.C. 返回列依赖关系Return column dependencies

下面的示例创建具有定义为列 Table1c 之和的计算列 a 的表 bThe following example creates the table Table1 with the computed column c defined as the sum of columns a and b. 然后 sys.dm_sql_referenced_entities 视图被调用。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);  
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  

下面是结果集:Here is the result set.

schema_name table_name referenced_column referencing_column  
----------- ---------- ----------------- ------------------  
dbo         Table1     a                 c  
dbo         Table1     b                 c  

D.D. 返回非绑定到架构的列依赖关系Returning non-schema-bound column dependencies

下面的示例删除 Table1 并创建 Table2 和存储过程 Proc1The following example drops Table1 and creates Table2 and stored procedure Proc1. 该过程引用 Table2 以及不存在的表 Table1The procedure references Table2 and the nonexistent table Table1. 视图 sys.dm_sql_referenced_entities 和指定为引用实体的存储过程一起运行。The view sys.dm_sql_referenced_entities is run with the stored procedure specified as the referencing entity. 结果集对于 Table1 显示一行,对于 Table2 显示 3 行。The result set shows one row for Table1 and 3 rows for Table2. 因为 Table1 不存在,所以列依赖关系无法解析,并返回错误 2020。Because Table1 does not exist, the column dependencies cannot be resolved and error 2020 is returned. is_all_columns_found 列为 Table1 返回 0,指示存在无法发现的列。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;
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,
        is_all_columns_found
    FROM
        sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO  

下面是结果集: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.E. 演示动态依赖关系维护Demonstrating dynamic dependency maintenance

此示例 E 假定已运行的示例 D。This Example E assumes that Example D has been run. 示例 E 演示动态维护依赖关系。Example E shows that dependencies are maintained dynamically. 该示例将执行以下操作:The example does the following things:

  1. 重新创建Table1,删除在示例 d。Re-creates Table1, which was dropped in Example D.
  2. 然后运行sys.dm_sql_referenced_entities与指定为引用实体的存储过程再次运行。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. 此外,is_all_columns_found 列为所有对象和列返回 1。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);  
GO   
SELECT
        referenced_id,
        referenced_entity_name AS table_name,
        referenced_minor_name  AS column_name,
        is_all_columns_found
    FROM
        sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO  
DROP TABLE Table1, Table2;  
DROP PROC Proc1;  
GO  

下面是结果集: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.F. 返回对象或列的用法Returning object or column usage

下面的示例返回存储过程 HumanResources.uspUpdateEmployeePersonalInfo 的对象和列依赖关系。The following example returns the objects and column dependencies of the stored procedure HumanResources.uspUpdateEmployeePersonalInfo. 此过程更新的列NationalIDNumberBirthDate,``MaritalStatus,并GenderEmployee表基于指定BusinessEntityID值。This procedure updates the columns NationalIDNumber, BirthDate,``MaritalStatus, and Gender of the Employee table based on a specified BusinessEntityID value. 另一个存储的过程,upsLogError中试一试定义...CATCH 块来捕获任何执行错误。Another stored procedure, upsLogError is defined in a TRY...CATCH block to capture any execution errors. is_selectedis_updatedis_select_all 列返回与如何在引用对象中使用这些对象和列有关的信息。The is_selected, is_updated, and is_select_all columns return information about how these objects and columns are used within the referencing object. 进行了修改的表和列由 is_updated 列中的 1 指示。The table and columns that are modified are indicated by a 1 in the is_updated column. 仅选择 BusinessEntityID 列,既不选择、也不修改存储过程 uspLogErrorThe BusinessEntityID column is only selected and the stored procedure uspLogError is neither selected nor modified.

USE AdventureWorks2012;
GO
SELECT
        referenced_entity_name AS table_name,
        referenced_minor_name  AS column_name,
        is_selected,  is_updated,  is_select_all
    FROM
        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.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)sys.sql_expression_dependencies (Transact-SQL)