[アーティクル]
10/25/2012

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 AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

このクエリでは、次のエラーメッセージが返されます。

Msg 207, Level 16, State 1, Procedure ApprovedDocuments, Line 3

Invalid column name 'ChangeNumber'.

Msg 2020, Level 16, State 1, Line 1

The dependencies reported for entity "Production.ApprovedDocuments" do not include references to 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.

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

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

USE AdventureWorks2008R2;
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();

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;

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 columnを 0 にリセットする必要があります。

Share via

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

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

ユーザー定義関数について is_ambiguous 列で報告される値が一貫しない

データベース間の依存関係について referenced_id 列が報告されない

データベース内の参照先エンティティについて referenced_id 列が NULL になる

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

関連項目

参照

概念

その他のリソース