sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)

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

针对当前数据库中按名称引用另一个用户定义实体的每个实体均返回一行。Returns one row for each entity in the current database that references another user-defined entity by name. 一个实体,调用时创建两个实体之间的依赖关系被引用的实体,在名为的另一个实体的持久化 SQL 表达式中按名称将显示引用实体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. 例如,如果将某个用户定义类型 (UDT) 指定为被引用的实体,则此函数将返回在其定义中按名称引用该类型的每个用户定义实体。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. 必须在 master 数据库的上下文中执行该函数,以便将服务器级 DDL 触发器作为引用实体返回。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

  • 数据库级 DDL 触发器Database-level DDL triggers

  • 服务器级 DDL 触发器Server-level DDL triggers

适用范围:SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)、SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), SQL 数据库SQL Database.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

schema_name.referencedentity_nameschema_name.referencedentity_name
是被引用实体的名称。Is the name of the referenced entity.

schema_name必需的当引用的类是 PARTITION_FUNCTION 时。schema_name is required except when the referenced class is PARTITION_FUNCTION.

schema_name.referenced_entity_namenvarchar(517)schema_name.referenced_entity_name is nvarchar(517).

< Referenced_class > :: = {对象 |类型 |XML_SCHEMA_COLLECTION |PARTITION_FUNCTION}<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 >nvarchar(60)。<referenced_class> is nvarchar(60).

返回的表Table Returned

列名Column name 数据类型Data type 描述Description
referencing_schema_namereferencing_schema_name sysnamesysname 引用实体所属的架构。Schema in which the referencing entity belongs. 可以为 Null。Is nullable.

对于数据库级和服务器级 DDL 触发器,为 NULL。NULL for database-level and server-level DDL triggers.
referencing_entity_namereferencing_entity_name sysnamesysname 引用实体的名称。Name of the referencing entity. 不可为 null。Is not nullable.
referencing_idreferencing_id intint 引用实体的 ID。ID of the referencing entity. 不可为 null。Is not nullable.
referencing_classreferencing_class tinyinttinyint 引用实体的类。Class of the referencing entity. 不可为 null。Is not nullable.

1 = 对象1 = Object

12 = 数据库级 DDL 触发器12 = Database-level DDL trigger

13 = 服务器级 DDL 触发器13 = Server-level DDL trigger
referencing_class_descreferencing_class_desc nvarchar(60)nvarchar(60) 引用实体的类的说明。Description of class of referencing entity.

OBJECTOBJECT

DATABASE_DDL_TRIGGERDATABASE_DDL_TRIGGER

SERVER_DDL_TRIGGERSERVER_DDL_TRIGGER
is_caller_dependentis_caller_dependent bitbit 指示被引用的实体的 ID 解析发生在运行时,因为它依赖于调用方的架构。Indicates the resolution of the referenced entity ID occurs at run time because it depends on the schema of the caller.

1 = 引用实体可能会引用该实体,但是,被引用的实体的 ID 解析依赖于调用方,因此不能确定此解析。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. 仅对于非绑定到架构的引用且被引用对象为存储过程、扩展存储过程或在 EXECUTE 语句中调用的用户定义函数时,才会出现这种情况。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 = 被引用的实体不依赖于调用方。0 = Referenced entity is not caller dependent.

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 referenced entity is a numbered stored procedure.

备注Remarks

下表列出了为其创建和维护依赖关系信息的实体类型。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

SQL Server 2008SQL Server 2008 - SQL Server 2012 (11.x)SQL Server 2012 (11.x)- SQL Server 2012 (11.x)SQL Server 2012 (11.x)

  • 需要对引用对象拥有 CONTROL 权限。Requires CONTROL permission on the referenced object. 当被引用的实体是分区函数时,要求对数据库拥有 CONTROL 权限。When the referenced entity is a partition function, CONTROL permission on the database is required.

  • 要求对 sys.dm_sql_referencing_entities 拥有 SELECT 权限。Requires SELECT permission on sys.dm_sql_referencing_entities. 默认情况下,SELECT 权限授予 public。By default, SELECT permission is granted to public.

SQL Server 2014 (12.x)SQL Server 2014 (12.x) - SQL Server 2017SQL Server 2017- SQL Server 2017SQL Server 2017

  • 不需要对引用对象拥有任何权限。Requires no permissions on the referenced object. 如果用户只对某些引用实体拥有 VIEW DEFINITION 权限,可能返回部分结果。Partial results can be returned if the user has VIEW DEFINITION on only some of the referencing entities.

  • 当引用实体为对象时,要求对对象拥有 VIEW DEFINITION 权限。Requires VIEW DEFINITION on the object when the referencing entity is an object.

  • 当引用实体为数据库级 DDL 触发器时,要求对数据库拥有 VIEW DEFINITION 权限。Requires VIEW DEFINITION on the database when the referencing entity is a database-level DDL trigger.

  • 当引用实体为服务器级 DDL 触发器时,要求对服务器拥有 VIEW ANY DEFINITION 权限。Requires VIEW ANY DEFINITION on the server when the referencing entity is a server-level DDL trigger.

示例Examples

A.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.B. 返回引用给定类型的实体Returning the entities that refer to a given type

下面的示例返回引用别名类型 dbo.Flag 的实体。The following example returns the entities that reference the alias type dbo.Flag. 结果集中显示了使用此类型的两个存储过程。The result set shows that two stored procedures use this type. dbo.Flag中的多个列的定义中还使用了类型HumanResources.Employee表; 但是,由于该类型不是计算的列、 CHECK 约束或 DEFAULT 约束的表中的定义中,不返回行的HumanResources.Employee表。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)`  

请参阅See Also

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