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

适用对象: yesSQL ServernoAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

当前数据库中用户定义实体的每个按名称依赖项在此表中均占一行。Contains one row for each by-name dependency on a user-defined entity in the current database. 这包括本机编译标量用户定义函数和其他SQL ServerSQL Server模块之间的依赖项。This includes dependences between natively compiled, scalar user-defined functions and other SQL ServerSQL Server modules. 两个实体之间的依赖关系是在另一个实体 (称为 "引用实体") 的持久化 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. 例如,在视图定义中引用表时,作为引用实体的视图将依赖于表这个被引用的实体。For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity. 如果删除该表,则该视图不可用。If the table is dropped, the view is unusable.

有关详细信息,请参阅内存中 OLTP 的标量用户定义函数For more information, see Scalar User-Defined Functions for In-Memory OLTP.

您可以使用此目录视图来报告以下实体的依赖关系信息:You can use this catalog view to report dependency information for the following entities:

  • 绑定到架构的实体。Schema-bound entities.

  • 非绑定到架构的实体。Non-schema-bound entities.

  • 跨数据库和跨服务器的实体。Cross-database and cross-server entities. 报告了实体名称;但实体 ID 尚未解析。Entity names are reported; however, entity IDs are not resolved.

  • 绑定到架构的实体的列级依赖关系。Column-level dependencies on schema-bound entities. 对于非绑定到架构的对象, 可以使用_sql_referenced_entities来返回这些对象的列级依赖关系。Column-level dependencies for non-schema-bound objects can be returned by using sys.dm_sql_referenced_entities.

  • 服务器级别的 DDL 触发器(在 master 数据库的上下文中时)。Server-level DDL triggers when in the context of the master database.

列名Column name 数据类型Data type 描述Description
referencing_idreferencing_id intint 引用实体的 ID。ID of the referencing entity. 不可为 null。Is not nullable.
referencing_minor_idreferencing_minor_id intint 引用实体为列时的列 ID;否则为 0。Column ID when the referencing entity is a column; otherwise 0. 不可为 null。Is not nullable.
referencing_classreferencing_class tinyinttinyint 引用实体的类。Class of the referencing entity.

1 = 对象或列1 = Object or column

12 = 数据库 DDL 触发器12 = Database DDL trigger

13 = 服务器 DDL 触发器13 = Server DDL trigger

不可为 null。Is not nullable.
referencing_class_descreferencing_class_desc nvarchar(60)nvarchar(60) 对引用实体的类的说明。Description of the class of referencing entity.

OBJECT_OR_COLUMNOBJECT_OR_COLUMN

DATABASE_DDL_TRIGGERDATABASE_DDL_TRIGGER

SERVER_DDL_TRIGGERSERVER_DDL_TRIGGER

不可为 null。Is not nullable.
is_schema_bound_referenceis_schema_bound_reference bitbit 1 = 被引用的实体绑定到架构。1 = Referenced entity is schema-bound.

0 = 被引用的实体未绑定到架构。0 = Referenced entity is non-schema-bound.

不可为 null。Is not nullable.
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

不可为 null。Is not nullable.
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

不可为 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 entities for which the entity was referenced without specifying a four-part name.

对于绑定到架构的实体, 此值为 NULL, 因为它们必须位于同一个数据库中, 因此只能使用由两个部分组成的名称 (schema) 来定义它们。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) 来定义它们。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 because schema-bound entities must be defined and referenced by using a two-part name.
referenced_entity_namereferenced_entity_name sysnamesysname 被引用的实体的名称。Name of the referenced entity. 不可为 null。Is not nullable.
referenced_idreferenced_id intint 被引用的实体的 ID。ID of the referenced entity. 对于绑定到架构的引用, 此列的值始终为 NULL。The value of this column is never NULL for schema-bound references. 对于跨服务器和跨数据库引用, 此列的值始终为 NULL。The value of this column is always NULL for cross-server and cross-database references.

对于数据库内的引用,如果无法确定 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 in the following cases:

被引用实体不存在于数据库中。The referenced entity does not exist in the database.

被引用的实体的架构依赖于调用方的架构,并在运行时解析。The schema of the referenced entity depends on the schema of the caller and is resolved at run time. 在这种情况下,is_caller_dependent 设置为 1。In this case, is_caller_dependent is set to 1.
referenced_minor_idreferenced_minor_id intint 引用实体为列时被引用的列的 ID;否则为 0。ID of the referenced column when the referencing entity is a column; otherwise 0. 不可为 null。Is not nullable.

当列在引用实体中按名称标识时,或者当 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.
is_caller_dependentis_caller_dependent bitbit 指示被引用的实体的架构绑定在运行时发生,因此,实体 ID 的解析依赖于调用方的架构。Indicates that schema binding for the referenced entity occurs at runtime; 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 a non-schema-bound user-defined function called in an EXECUTE statement.

1 = 被引用的实体依赖于调用方并在运行时解析。1 = The referenced entity is caller dependent and is resolved at runtime. 在这种情况下,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) 或对xml类型的列的 xquery 引用。Indicates 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 that 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 = 引用不明确。1 = Reference is ambiguous.

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

对于架构绑定引用, 始终为0。Always 0 for schema bound references.

备注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.

备注

Azure SQL 数据仓库和并行数据仓库支持表、视图、筛选统计信息以及此列表中的 Transact-sql 存储过程实体类型。Azure SQL Data Warehouse and Parallel Data Warehouse support tables, views, filtered statistics, and Transact-SQL stored procedures entity types from this list. 只为表、视图和筛选统计信息创建并维护依赖关系信息。Dependency information is created and maintained for tables, views, and filtered statistics only.

实体类型Entity type 引用实体Referencing entity 被引用的实体Referenced entity
Table 是*Yes* Yes
视图View Yes Yes
筛选索引Filtered index 是**Yes** No
筛选统计信息Filtered statistics 是**Yes** No
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计算列、CHECK 约束或 DEFAULT 约束的定义中的模块、用户定义类型或 XML 架构集合时, 才会将该表作为引用实体进行跟踪。* 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.

** 筛选谓词中使用的每列都作为引用实体进行跟踪。** Each column used in the filter predicate is tracked as a referencing entity.

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

权限Permissions

要求对数据库具有 VIEW DEFINITION 权限,并对数据库的 sys.sql_expression_dependencies 具有 SELECT 权限。Requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. 默认情况下,SELECT 权限仅授予 db_owner 固定数据库角色的成员。By default, SELECT permission is granted only to members of the db_owner fixed database role. 将 SELECT 和 VIEW DEFINITION 权限授予其他用户时,被授权者可以查看数据库中的所有依赖关系。When SELECT and VIEW DEFINITION permissions are granted to another user, the grantee can view all dependencies in the database.

示例Examples

A.A. 返回另一实体引用的实体Returning entities that are referenced by another entity

下例返回在 Production.vProductAndDescription 视图中引用的表和列。The following example returns the tables and columns referenced in the view Production.vProductAndDescription. 该视图依赖于 referenced_entity_namereferenced_column_name 列中返回的实体(表和列)。The view depends on the entities (tables and columns) returned in the referenced_entity_name and referenced_column_name columns.

USE AdventureWorks2012;  
GO  
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_desciption,   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
    referencing_class_desc,  
    referenced_server_name, referenced_database_name, referenced_schema_name,  
    referenced_entity_name,   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
    is_caller_dependent, is_ambiguous  
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');  
GO  
  

B.B. 返回引用其他实体的实体Returning entities that reference another entity

下面的示例返回引用表 Production.Product 的实体。The following example returns the entities that reference the table Production.Product. referencing_entity_name 列中返回的实体依赖于 Product 表。The entities returned in the referencing_entity_name column depend on the Product table.

USE AdventureWorks2012;  
GO  
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,  
    OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_desciption,   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
    referencing_class_desc, referenced_class_desc,  
    referenced_server_name, referenced_database_name, referenced_schema_name,  
    referenced_entity_name,   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
    is_caller_dependent, is_ambiguous  
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referenced_id = OBJECT_ID(N'Production.Product');  
GO  
  

C.C. 返回跨数据库依赖关系Returning cross-database dependencies

下面的示例返回所有跨数据库的依赖关系。The following example returns all cross-database dependencies. 此示例首先创建数据库 db1 以及两个引用数据库 db2db3 中的表的存储过程。The example first creates the database db1 and two stored procedures that reference tables in the databases db2 and db3. 然后,对 sys.sql_expression_dependencies 表进行查询,以报告这两个过程和表之间的跨数据库依赖关系。The sys.sql_expression_dependencies table is then queried to report the cross-database dependencies between the procedures and the tables. 请注意,在被引用实体 referenced_schema_namet3 列中返回 NULL,原因是过程定义中没有为该实体指定架构名称。Notice that NULL is returned in the referenced_schema_name column for the referenced entity t3 because a schema name was not specified for that entity in the definition of the procedure.

CREATE DATABASE db1;  
GO  
USE db1;  
GO  
CREATE PROCEDURE p1 AS SELECT * FROM db2.s1.t1;  
GO  
CREATE PROCEDURE p2 AS  
    UPDATE db3..t3  
    SET c1 = c1 + 1;  
GO  
SELECT OBJECT_NAME (referencing_id),referenced_database_name,   
    referenced_schema_name, referenced_entity_name  
FROM sys.sql_expression_dependencies  
WHERE referenced_database_name IS NOT NULL;  
GO  
USE master;  
GO  
DROP DATABASE db1;  
GO  
  

请参阅See Also

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