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

適用於: 是SQL Server 否Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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. 雖然系統會報告實體名稱,但是不會解析實體識別碼。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 of the referencing entity. 不可為 Null。Is not nullable.
referencing_minor_idreferencing_minor_id intint 當參考實體是資料行時,就是資料行識別碼,否則便是 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

類型TYPE

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.

因為架構系結的實體必須位於相同的資料庫中, 因此只能使用兩部分 (schema. object) 名稱加以定義, 所以為 Null。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.

因為架構系結的實體必須位於相同的資料庫中, 因此只能使用兩部分 (schema. object) 名稱加以定義, 所以為 Null。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 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.

無法判斷識別碼時,若為資料庫中的參考,則為 NULL。NULL for references within the database if the ID cannot be determined. 若為非結構描述繫結參考,便無法在下列情況中解析識別碼: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 當參考實體是資料行時,就是受參考資料行的識別碼,否則便是 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 指出在執行階段發生之受參考實體的結構描述繫結。因此,實體識別碼的解析會相依於呼叫端的結構描述。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 = 受參考的實體識別碼不是呼叫端相依。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.

PermissionsPermissions

需要資料庫的 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. 該範例會先在資料庫 db1db2 中,建立參考資料表的資料庫 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_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)