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

• 03/14/2017
• • W
  • o

适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel 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. 使用 sys.dm_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.

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

_ 仅当表引用 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_name 和 referenced_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 以及两个引用数据库 db2 和 db3 中的表的存储过程。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_name 的 t3 列中返回 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)