sys.dm_sql_referenced_entities (Transact-SQL)

文章
07/05/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

針對 SQL Server 中指定參考實體的定義中，依名稱所參考的每個使用者定義實體，各傳回一個數據列。當一個稱為 參考實體的使用者定義實體出現在另一個使用者定義實體的保存 SQL 運算式中，稱為參考實體時，就會建立兩個實體之間的相依性。例如，如果預存程式是指定的參考實體，此函式會傳回預存程式中參考的所有使用者定義實體，例如數據表、檢視表、使用者定義型別（UDT）或其他預存程式。

您可以使用這個動態管理函式來報告指定參考實體所參考的下列實體類型：

架構系結實體
非架構系結實體
跨資料庫和跨伺服器實體
架構系結和非架構系結實體的數據行層級相依性
使用者定義型別（別名和 CLR UDT）
XML 結構描述集合
資料分割函數

語法

sys.dm_sql_referenced_entities (  
    ' [ schema_name. ] referencing_entity_name ' ,
    ' <referencing_class> ' )  
  
<referencing_class> ::=  
{  
    OBJECT  
  | DATABASE_DDL_TRIGGER  
  | SERVER_DDL_TRIGGER  
}

引數

[ schema_name。 ] referencing_entity_name
這是參考實體的名稱。當參考類別為 OBJECT 時，需要schema_name。

schema_name.referencing_entity_name 是 nvarchar（517）。

<referencing_class> ：：= { OBJECT |DATABASE_DDL_TRIGGER |SERVER_DDL_TRIGGER }
這是指定參考實體的類別。每個語句只能指定一個類別。

<> referencing_class是 nvarchar（60）。

傳回的資料表

資料行名稱	資料類型	描述
referencing_minor_id	int	參考實體為數據行時的數據行標識元;否則為 0。不可為 Null。
referenced_server_name	sysname	所參考實體的伺服器名稱。這個數據行會填入透過指定有效的四部分名稱所建立的跨伺服器相依性。如需多部分名稱的詳細資訊，請參閱 Transact-SQL 語法慣例。參考實體之非架構系結相依性的 NULL，而不需指定四部分的名稱。架構系結實體的 NULL，因為它們必須位於相同的資料庫中，因此只能使用兩部分（schema.object）名稱來定義。
referenced_database_name	sysname	所參考實體的資料庫名稱。這個數據行會填入跨資料庫或跨伺服器參考，這些參考是藉由指定有效的三部分或四部分名稱所建立。使用一部分或兩部分名稱指定時，非架構系結參考的NULL。架構系結實體的 NULL，因為它們必須位於相同的資料庫中，因此只能使用兩部分（schema.object）名稱來定義。
referenced_schema_name	sysname	參考實體所屬的架構。在未指定架構名稱的情況下參考實體的非架構系結參考 NULL。架構系結參考絕不為 NULL。
referenced_entity_name	sysname	參考實體的名稱。不可為 Null。
referenced_minor_name	sysname	參考實體為數據行時的數據行名稱;否則為 NULL。例如，referenced_minor_name是列出參考實體本身的數據列中的NULL。當參考實體中以名稱識別數據行，或當 SELECT * 語句中使用父實體時，參考實體就是數據行。
referenced_id	int	參考實體的標識碼。當referenced_minor_id不是 0 時，referenced_id是定義數據行的實體。跨伺服器參考的一律為 NULL。無法判斷標識符時，因為資料庫已離線或實體無法系結，所以無法判斷跨資料庫參考的NULL。如果無法判斷標識碼，則為資料庫中的參考NULL。若為非架構系結參考，當參考的實體不存在於資料庫中或名稱解析相依時，便無法解析標識符。在後者的情況下，is_caller_dependent設定為 1。架構系結參考絕不為 NULL。
referenced_minor_id	int	參考實體為數據行時的數據行標識元;否則為 0。例如，referenced_minor_is在列出參考實體本身的數據列中是0。針對非架構系結參考，只有在所有參考的實體都可以系結時，才會報告數據行相依性。如果無法系結任何參考的實體，則不會報告任何數據行層級相依性，且referenced_minor_id為0。請參閱範例 D。
referenced_class	tinyint	參考實體的類別。 1 = 對象或資料行 6 = 類型 10 = XML 架構集合 21 = 資料分割函數
referenced_class_desc	nvarchar(60)	參考實體類別的描述。 OBJECT_OR_COLUMN TYPE XML_SCHEMA_COLLECTION PARTITION_FUNCTION
is_caller_dependent	bit	表示參考實體的架構系結會在運行時間發生;因此，實體標識碼的解析取決於呼叫者的架構。當參考的實體是 EXECUTE 語句內呼叫的預存程式、擴充預存程式或使用者定義函數時，就會發生這種情況。 1 = 參考的實體相依於呼叫端，並在運行時間解析。在此情況下，referenced_id為 NULL。 0 = 參考的實體識別碼與呼叫端無關。針對架構系結參考，以及明確指定架構名稱的跨資料庫和跨伺服器參考，一律為 0。例如，格式 `EXEC MyDatabase.MySchema.MyProc` 實體的參考不是呼叫端相依。不過，格式 `EXEC MyDatabase..MyProc` 的參考是呼叫端相依的。
is_ambiguous	bit	表示參考模棱兩可，而且可以在運行時間解析為使用者定義函數、用戶定義型別（UDT），或 xml 類型的 xquery 參考。例如，假設語句 `SELECT Sales.GetOrder() FROM Sales.MySales` 是在預存程式中定義。在執行預存程式之前，不知道是`Sales.GetOrder()`名為之方法之架構或`Sales`類型 UDT `GetOrder()`資料行中的`Sales`使用者定義函式。 1 = 使用者定義函數或資料行使用者定義型別（UDT）方法的參考模棱兩可。 0 = 參考是明確的，或者呼叫函式時，可以成功系結實體。架構系結參考的一律為 0。
is_selected	bit	1 = 已選取物件或數據行。
is_updated	bit	1 = 修改物件或資料行。
is_select_all	bit	1 = 對象用於 SELECT * 子句（僅限物件層級）。
is_all_columns_found	bit	1 = 可以找到物件的所有資料行相依性。 0 = 找不到物件的數據行相依性。
is_insert_all	bit	1 = 對象用於不含數據行清單的 INSERT 語句中（僅限物件層級）。此數據行已在 SQL Server 2016 中新增。
is_incomplete	bit	1 = 對象或數據行發生系結錯誤且不完整。此數據行已在 SQL Server 2016 SP2 中新增。

例外狀況

傳回下列任一條件下的空白結果集：

系統物件已指定。
指定的實體不存在於目前的資料庫中。
指定的實體不會參考任何實體。
傳遞無效的參數。

當指定的參考實體是編號的預存程式時，傳回錯誤。

無法解析數據行相依性時，傳回錯誤 2020。此錯誤不會防止查詢傳回物件層級相依性。

備註

此函式可以在任何資料庫的內容中執行，以傳回參考伺服器層級 DDL 觸發程式的實體。

下表列出建立和維護相依性資訊的實體類型。不會針對規則、預設值、臨時表、暫存程式或系統物件建立或維護相依性資訊。

實體類型	參考實體	參考的實體
Table	是*	Yes
檢視表	Yes	Yes
Transact-SQL 預存程式**	Yes	Yes
CLR 預存程式	No	Yes
Transact-SQL 用戶定義函數	Yes	Yes
CLR 使用者定義函數	No	Yes
CLR 觸發程式（DML 和 DDL）	No	No
Transact-SQL DML 觸發程式	是	No
Transact-SQL 資料庫層級 DDL 觸發程式	是	No
Transact-SQL 伺服器層級 DDL 觸發程式	是	No
擴充預存程序	No	Yes
Queue	No	Yes
同義字	No	Yes
類型（別名和 CLR 使用者定義類型）	No	Yes
XML 結構描述集合	No	Yes
分割區函數	No	Yes

* 只有在數據表參考計算數據行、CHECK 條件約束或 DEFAULT 條件約束的定義中參考 Transact-SQL 模組、使用者定義型別或 XML 架構集合時，才會將數據表追蹤為參考實體。

** 具有大於 1 之整數值的編號預存程式不會追蹤為參考或參考實體。

權限

需要 sys.dm_sql_referenced_entities 的 SELECT 權限和參考實體的 VIEW DEFINITION 權限。根據預設，SELECT 權限會授與 public。當參考實體為資料庫層級 DDL 觸發程序時，便需要資料庫的 VIEW DEFINITION 權限，或資料庫的 ALTER DATABASE DDL TRIGGER 權限。當參考實體為伺服器層級 DDL 觸發程序時，便需要伺服器的 VIEW ANY DEFINITION 權限。

範例

A. 傳回資料庫層級 DDL 觸發程式所參考的實體

下列範例會傳回資料庫層級 DDL 觸發程式 ddlDatabaseTriggerLog所參考的實體（數據表和數據行）。

USE AdventureWorks2022;  
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. 傳回物件所參考的實體

下列範例會傳回使用者定義函數 dbo.ufnGetContactInformation所參考的實體。

USE AdventureWorks2022;  
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. 傳回數據行相依性

下列範例會建立數據表 Table1 ，其中計算數據行 c 定義為數據行和的總 a 和 b。然後呼叫檢視 sys.dm_sql_referenced_entities 。檢視會傳回兩個數據列，其中一個用於計算數據行中定義的每個數據行。

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

結果集如下所示。

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

D. 傳回非架構系結數據行相依性

下列範例會卸除 Table1 並建立 Table2 和預存程式 Proc1。程式參考 Table2 與不存在的資料表 Table1。檢視 sys.dm_sql_referenced_entities 是使用指定為參考實體的預存程式來執行。結果集會顯示的一個數據列，而針對顯示 3 個數據Table2列Table1。因為 Table1 不存在，所以無法解析數據行相依性，並傳回錯誤 2020。數據行會 is_all_columns_found 傳回 0， Table1 表示找不到資料行。

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

結果集如下所示。

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 假設已執行範例 D。範例 E 顯示以動態方式維護相依性。此範例會執行下列動作：

重新建立 Table1，其已在範例 D 中卸除。
執行 Then sys.dm_sql_referenced_entities 會以指定為參考實體的預存程式再次執行。

結果集顯示會傳回數據表及其預存程式中定義的個別數據行。此外，數據 is_all_columns_found 行會針對所有對象和數據行傳回 1。

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

結果集如下所示。

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. 傳回物件或數據行使用方式

下列範例會傳回預存程式 HumanResources.uspUpdateEmployeePersonalInfo的物件和數據行相依性。此程式會根據指定的值來更新NationalIDNumber資料表的數據Employee列、 BirthDate,``MaritalStatus和 Gender 。BusinessEntityID 另一個預存程式是在 upsLogError TRY 中定義...CATCH 區塊可擷取任何執行錯誤。 is_selected、 is_updated和數據is_select_all行會傳回參考物件內使用這些物件和數據行的相關信息。修改的數據表和數據行會以is_updated數據行中的 1 表示。只會選取數據 BusinessEntityID 行，而且預存程式 uspLogError 不會選取或修改。

USE AdventureWorks2022;
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')
;

結果集如下所示。

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

另請參閱

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

分享方式：

sys.dm_sql_referenced_entities (Transact-SQL)

語法

引數

傳回的資料表

例外狀況

備註

權限

範例

A. 傳回資料庫層級 DDL 觸發程式所參考的實體

B. 傳回物件所參考的實體

C. 傳回數據行相依性

D. 傳回非架構系結數據行相依性

E. 示範動態相依性維護

F. 傳回物件或數據行使用方式

另請參閱

意見反映

更多資源

分享方式：

sys.dm_sql_referenced_entities (Transact-SQL)

語法

引數

傳回的資料表

例外狀況

備註

權限

範例

A. 傳回資料庫層級 DDL 觸發程式所參考的實體

B. 傳回 物件所參考的實體

C. 傳回數據行相依性

D. 傳回非架構系結數據行相依性

E. 示範動態相依性維護

F. 傳回物件或數據行使用方式

另請參閱

意見反映

更多資源

B. 傳回物件所參考的實體