• [アーティクル]

SQL の依存関係のトラブルシューティング

このトピックでは、オブジェクトの依存関係に関する一般的な問題とその解決方法について説明します。

動的管理関数 sys.dm_sql_referenced_entities から列レベルの依存関係が返されない

sys.dm_sql_referenced_entities システム関数では、スキーマ バインド参照の列レベルの依存関係が報告されます。たとえば、インデックス付きビューの列レベルの依存関係がすべて報告されます。これは、インデックス付きビューがスキーマ バインドを必要とするためです。ただし、参照先エンティティがスキーマにバインドされていない場合は、列が参照されているすべてのステートメントをバインドできる場合にのみ、列の依存関係が報告されます。ステートメントをバインドできるのは、ステートメント解析時にすべてのオブジェクトが存在している場合に限られます。エンティティに定義されているステートメントを 1 つでもバインドできない場合は、列の依存関係が報告されず、referenced_minor_id 列で 0 が返されます。列の依存関係を解決できない場合は、エラー 2020 が発生します。このエラーによって、クエリからオブジェクト レベルの依存関係が返されなくなることはありません。

解決方法

エラー 2020 発生前のメッセージで確認されたエラーをすべて修正します。たとえば次のコード例では、Production.Document テーブルの Title、ChangeNumber、Status の各列で、ビュー Production.ApprovedDocuments が定義されています。sys.dm_sql_referenced_entities システム関数で、ApprovedDocuments ビューが依存するオブジェクトと列が照会されます。このビューの作成には 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" に対してレポートされた依存関係に、列への参照が含まれていません。この原因としては、存在しないオブジェクトがエンティティによって参照されていること、またはエンティティの 1 つ以上のステートメントにエラーがあることが挙げられます。クエリを再実行する前に、エンティティにエラーがないこと、およびエンティティによって参照されているすべてのオブジェクトが存在することを確認してください。

次の例では、ビュー内の列名を修正しています。

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 スキーマ内のユーザー定義関数なのか、GetOrder() という名前のメソッドを持つ UDT 型の Sales という名前の列なのかが不明です。この場合は、参照先エンティティ Sales.GetOrder() について is_ambiguous 列が 1 に設定されます。2 番目の SELECT ステートメントでは、Sales.GetOrder() への参照は明確です。つまり、構文に基づいて、必ずユーザー定義関数への参照に解決されます。この場合、is_ambiguous 列は 0 に設定されます。この動作のために、is_ambiguous 列で報告される値が一貫していないように見える場合があります。is_ambiguous 列の値がどのように決定されるかを理解することで、報告された値を解明できます。

is_ambiguous 列が 0 (false) に設定されるのは次の場合です。

  • 参照先がユーザー定義関数であることが明らかな場合。つまり、クエリがユーザー定義関数にバインドされており、同じ名前の列の UDT メソッドまたは xml 型の列が存在しない場合です。

  • 参照先が列の UDT メソッドであることが明らかな場合。つまり、その UDT メソッドを持つ列が存在し、同じ名前のユーザー定義関数または xml 型の列が存在しない場合です。

is_ambiguous 列が 1 (true) に設定されるのは次の場合です。

  • 参照されている名前を持つユーザー定義関数、列の 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;

この関数では、hierarchyid UDT メソッド呼び出しが原因で、referenced_database_name 列と referenced_schema_name 列が無効になります。h.empid.GetDescendant および h1.empid.GetAncestor への参照が、3 部構成の名前 (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 システム ビューでは、スキーマ バインド参照先エンティティの ID が報告されます。ただし、参照先エンティティの ID を特定できない場合は、データベース内の非スキーマ バインド参照について referenced_id 列が NULL になります。これには次の原因が考えられます。

  • 参照先エンティティがデータベースに存在しない。

  • 名前解決が呼び出し元に依存する。この場合は、is_caller_dependent 列が 1 に設定されます。

解決方法

参照先エンティティがデータベースに存在することを確認します。存在しない場合はエンティティを作成し、存在する場合は以下の要件が満たされていることを確認します。

  • 参照先エンティティ名のスペルが正しいこと。

  • 指定した名前がデータベースの照合順序の要件を満たしていること。大文字と小文字が区別される照合順序がデータベースで使用されている場合は、指定した名前とオブジェクト名の大文字と小文字が正確に一致している必要があります。たとえば、SalesHistory という名前のオブジェクトを saleshistory と指定すると、大文字と小文字が区別される照合順序を使用するデータベースではそのオブジェクトの ID が検出されません。

  • オブジェクトのスキーマ名が指定されていること。呼び出し元の既定のスキーマ、sys スキーマ、または dbo スキーマにオブジェクトがない場合は、2 部構成の名前 (schema_name.object_name) が必要です。

上記の要件を満たすように、参照元エンティティの定義を変更します。

参照先エンティティが呼び出し元に依存する場合は、参照先エンティティの 2 部構成の名前を指定することで、参照元エンティティの定義を変更します。呼び出し元に依存する参照の詳細については、「SQL 依存関係のレポート」を参照してください。

master データベース内のオブジェクトについて依存関係情報が報告されない

master データベースで作成されたユーザー定義エンティティには、SQL の依存関係が作成され、管理されています。エンティティの SQL 依存関係が報告されない場合は、次の手順を実行します。

  • エンティティが依存関係を追跡される種類であることを確認します。

    依存関係情報は、すべてのユーザー オブジェクトについて追跡されるわけではありません。依存関係情報が作成および管理されるエンティティの種類については、「SQL の依存関係について」を参照してください。

  • エンティティがシステム オブジェクトとして設定されていないことを確認します。

    sys.objects カタログ ビューで、エンティティの is_ms_shipped 列を照会します。この列が 1 に設定されている場合は、エンティティが SQL Server 付属のシステム オブジェクトであるか、またはこの列を手動で 1 に設定することでユーザー定義オブジェクトが擬似的なシステム オブジェクトに変更されています。

解決方法

オブジェクトがサポートされていない種類である場合は、依存関係情報が存在しません。

システム オブジェクトの依存関係は追跡されません。エンティティがユーザー定義である場合に、その依存関係を SQL Server で作成して管理する必要がある場合は、is_ms_shipped 列を 0 にリセットする必要があります。