Share via

  • 發行項

疑難排解 SQL 相依性

這個主題將描述常見的物件相依性問題及其解決方案。

動態管理函數 sys.dm_sql_referenced_entities 沒有傳回資料行層級相依性

sys.dm_sql_referenced_entities 系統函數將會報告結構描述繫結參考的任何資料行層級相依性。例如，此函數將會報告索引檢視表的所有資料行層級相依性，因為索引檢視表需要結構描述繫結。不過，如果受參考實體並未結構描述繫結，只有當參考資料行的所有陳述式都可以繫結時，才會報告資料行相依性。只有當剖析陳述式時所有物件都存在時，陳述式才能成功繫結。如果實體中定義的任何陳述式無法繫結，將不會報告資料行相依性，而且 referenced_minor_id 資料行將傳回 0。無法解析資料行相依性時，就會引發錯誤 2020。這個錯誤不會讓查詢無法傳回物件層級相依性。

解決方案

更正發生錯誤 2020 之前，在訊息中識別的任何錯誤。例如，在下列程式碼範例中，Production.ApprovedDocuments 檢視表定義於 Production.Document 資料表中的 Title、ChangeNumber 和 Status 資料行上。系統會針對 ApprovedDocuments 檢視表所相依的物件和資料行查詢 sys.dm_sql_referenced_entities 系統函數。因為此檢視表並非使用 WITH SCHEMA_BINDING 子句建立，所以檢視表中參考的資料行可能會在參考的資料表中修改。此範例會將 Production.Document 資料表中的 ChangeNumber 資料行重新命名為 TrackingNumber，藉以更改此資料行。系統會再次針對 ApprovedDocuments 檢視表查詢目錄檢視。不過，它無法繫結至檢視表中定義的所有資料行。然後，系統會傳回識別此問題的錯誤 207 和 2020。若要解決此問題，您必須更改檢視表，以便反映資料行的新名稱。

USE AdventureWorks;
GO
CREATE VIEW Production.ApprovedDocuments
AS
    SELECT Title, ChangeNumber, Status
    FROM Production.Document
    WHERE Status = 2;
GO
SELECT referenced_schema_name AS schema_name
    ,referenced_entity_name AS table_name
    ,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
EXEC sp_rename 'Production.Document.ChangeNumber', 'TrackingNumber', 'COLUMN';
GO
SELECT referenced_schema_name AS schema_name
    ,referenced_entity_name AS table_name
    ,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO

此查詢會傳回下列錯誤訊息。

訊息 207，層級 16，狀態 1，程序 ApprovedDocuments，行 3

無效的資料行名稱 'ChangeNumber'。

訊息 2020，層級 16，狀態 1，行 1

針對實體 "Production.ApprovedDocuments" 所報告的相依性不包含資料行的參考。這是因為此實體參考了不存在的物件，或是此實體中的一或多個陳述式發生錯誤。在重新執行查詢以前，請確定此實體中沒有任何錯誤，而且此實體參考的所有物件都存在。

下列範例會更正檢視表中的資料行名稱。

USE AdventureWorks;
GO
ALTER VIEW Production.ApprovedDocuments
AS
    SELECT Title,TrackingNumber, Status
    FROM Production.Document
    WHERE Status = 2;
GO

is_ambiguous 資料行針對使用者定義函數報告不一致的值

在 is_ambiguous 資料行中，針對使用者定義函數報告的值似乎不一致。sys.sql_expression_dependencies 目錄檢視和 sys.dm_sql_referenced_entities 動態函數中的 is_ambiguous 資料行表示實體的參考模稜兩可。也就是說，實體可以在執行階段解析成使用者定義函數、使用者定義型別 (UDT)，或 xml 類型資料行的 XQuery 參考。根據使用者定義函數的參考方式而定，實體類型不一定明確，而這可能會導致 is_ambiguous 資料行在某個情況下設定為 1 (true)，但在另一個情況下設定為 0 (false)。例如，請考慮下列預存程序。

CREATE PROCEDURE dbo.p1 
AS
    SELECT Sales.GetOrder() FROM t1;
    SELECT Sales.GetOrder();

在第一個 SELECT 陳述式中，不確定 Sales.GetOrder() 是 Sales 結構描述中的使用者定義函數，還是名為 Sales、類型是 UDT 而且具有名為 GetOrder() 之方法的資料行。在此情況下，is_ambiguous 資料行將針對受參考實體 Sales.GetOrder() 設定為 1。在第二個 SELECT 陳述式中，Sales.GetOrder() 的參考很明確。根據語法，它只能是使用者定義函數的參考。在此情況下，is_ambiguous 資料行會設定為 0。這種行為可能會讓 is_ambiguous 資料行中報告的值看似不一致。了解如何判斷 is_ambiguous 資料行的值可以釐清報告的值。

在下列情況中，is_ambiguous 資料行會設定為 0 (false)：

  • 確定參考指向使用者定義函數。也就是說，查詢繫結至使用者定義函數，而且具有該名稱的資料行 UDT 方法或 xml 類型資料行不存在。

  • 確定參考指向資料行 UDT 方法。也就是說，具有該 UDT 方法的資料行存在，而且具有該名稱的使用者定義函數或 xml 類型資料行不存在。

在下列情況中，is_ambiguous 資料行會設定為 1 (false)：

  • 具有參考名稱的使用者定義函數、資料行 UDT 方法或 xml 類型資料行不存在。

  • 參考的名稱會針對多個實體存在。例如，使用者定義函數和資料行 UDT 方法具有相同的名稱。

若為本質就模稜兩可的實體，referenced_database_name 和 referenced_schema_name 資料行可能會無效。例如，請考慮下列使用者定義函數：

CREATE FUNCTION GetNextEmpHierarchyId (@empname varchar(25))
RETURNS hierarchyid
AS
BEGIN
    RETURN 
(
    SELECT h.empid.GetDescendant((SELECT MAX(h1.empid)  
                                  FROM dbo.Employees AS h1  
                                  WHERE h1.empid.GetAncestor(1) = h.empid), NULL)
    FROM dbo.Employees AS h  
    WHERE h.empname = @empname  
)  
END;

對於此函數而言，referenced_database_name 和 referenced_schema_name 資料行由於 hierarchyid UDT 方法呼叫而無效。不確定 h.empid.GetDescendant 和 h1.empid.GetAncestor 的參考是使用三部分名稱 (database.schema.object) 之實體的參考，還是 UDT 方法 (table.column.method) 的參考。

解決方案

不需要任何使用者動作。

不會針對跨資料庫相依性報告 referenced_id 資料行

系統永遠不會針對 sys.sql_expression_dependencies 目錄檢視中的跨資料庫參考解析 referenced_id 資料行。只有在明確指定名稱時，才會記錄資料庫名稱和結構描述名稱。例如，指定為 MyDB.MySchema.MyTable 時，就會記錄資料庫名稱和結構描述名稱。不過，指定為 MyDB..MyTable 時，則只會記錄資料庫名稱。

只有當受參考實體可以成功繫結時，系統才會針對 sys.dm_sql_referenced_entities 系統函數中的跨資料庫參考報告 referenced_id。繫結可能會因為許多原因而失敗，包括下列原因：

  • 資料庫已離線。

  • 受參考實體不存在資料庫中。

解決方案

確認資料庫已上線，而且受參考實體存在資料庫中。

對於資料庫內部的受參考實體而言，referenced_id 資料行為 Null

sys.dm_sql_referenced_entities 系統函數和 sys.sql_expression_dependencies 系統檢視表將會報告任何結構描述繫結之受參考實體的識別碼。不過，無法判斷受參考實體的識別碼時，對於資料庫內部的非結構描述繫結參考而言，referenced_id 資料行為 NULL。可能發生的原因有：

  • 受參考實體不存在資料庫中。

  • 名稱解析是呼叫者相依。在此情況下，is_caller_dependent 資料行會設定為 1。

解決方案

確認受參考實體存在資料庫中。如果找不到此實體，請建立它。如果此實體已存在，請確定符合下列需求：

  • 受參考實體名稱的拼字正確。

  • 指定的名稱符合資料庫的定序需求。如果資料庫使用區分大小寫的定序，指定的名稱就必須與資料庫名稱的大小寫完全相符。例如，如果指定為 saleshistory，便無法在具有區分大小寫定序的資料庫中找到名為 SalesHistory 之物件的識別碼。

  • 已指定物件的結構描述名稱。如果此物件不在呼叫者的預設結構描述、sys 結構描述或 dbo 結構描述中，就需要使用兩部分名稱 (schema_name.object_name)。

請修改參考實體的定義來符合上述需求。

如果受參考實體是呼叫者相依，請透過指定兩部分名稱給受參考實體，修改參考實體的定義。如需有關呼叫者相依之參考的詳細資訊，請參閱＜報告 SQL 相依性＞。

不會針對 master 資料庫中的物件報告相依性資訊

系統會建立並維護在 master 資料庫中建立之使用者定義實體的 SQL 相依性。如果沒有報告某個實體的 SQL 相依性，請遵循下列步驟：

  • 確定此實體是相依性追蹤的有效類型。

    系統不會針對所有使用者物件追蹤相依性資訊。如需建立並維護相依性資訊之實體類型的清單，請參閱＜了解 SQL 相依性＞。

  • 確定此實體並未標示為系統物件。

    查詢 sys.objects 目錄檢視中實體的 is_ms_shipped 資料行。如果這個資料行設定為 1，表示此實體是隨附於 SQL Server 的系統物件，或者它是已經修改成模擬系統物件 (手動將此資料行設定為 1) 的使用者定義物件。

解決方案

如果此物件屬於不支援的類型，就無法取得相依性資訊。

系統不會追蹤系統物件的相依性。如果此實體是使用者定義的，而且您想讓 SQL Server 建立並維護此實體的相依性，is_ms_shipped 資料行就必須重設為 0。