[アーティクル]
09/28/2010

SQL 依存関係のレポート

SQL の依存関係とは、あるユーザー定義エンティティを別のエンティティに依存させる SQL 式の中で使用される、名前による参照です。たとえば、ビューとストアドプロシージャは、そのビューまたはプロシージャが返すデータを格納するテーブルの存在に依存します。依存関係情報のレポートは、次のシナリオで役立ちます。

ストアドプロシージャなどのモジュールをアプリケーション間で移動する場合。

モジュールを移動する前に、そのモジュールがデータベースエンティティまたはクロスデータベースエンティティを参照しているかどうかを確認できます。参照している場合は、モジュールと共にそれらのエンティティも移動する必要があります。
テーブルに列を追加する、テーブルから列を削除するなど、エンティティの定義を変更する場合。

エンティティを変更する前に、そのエンティティの現在の定義に依存するエンティティが他にあるかどうかを確認できます。変更後に、この依存しているエンティティにクエリを実行したりこのエンティティを呼び出したりすると、予期しない結果が生じる可能性があり、メタデータの更新やエンティティの定義の変更が必要となる場合があります。
1 つまたは複数のデータベースをサーバー間で移動する場合。

データベースを別のサーバーに移動する前に、あるデータベースのエンティティが別のデータベースのエンティティに依存しているかどうかを確認できます。依存関係がある場合は、両方のデータベースを同じサーバーに移動する必要があります。
複数のデータベースにまたがるアプリケーションのフェールオーバーを構成する場合。

アプリケーションの可用性を確保するため、通常、フェールオーバー方式としてデータベースミラーリングを使用します。データベースミラーリングでは複数のデータベースが必要になります。つまり、ミラーサーバーにフェールオーバーした場合でも、アプリケーションが正常に実行されなければなりません。ミラーリングはデータベースレベルで機能するので、そのアプリケーションにとって不可欠なデータベースを判断し、それらすべてのデータベースに対してミラーリングを個別に設定する必要があります。これで、すべてのデータベースが一度にフェールオーバーされるようになり、アプリケーションがミラーサーバーでも動作するようになります。
アプリケーションで、4 部構成の名前を使用する分散クエリを実行するエンティティを検索する場合。

分散クエリで使用されているリンクサーバーを確認できます。
アプリケーションで使用されている、呼び出し元に依存する参照または 1 部構成の名前の参照を含んでいるエンティティを検索する場合。

アプリケーションを配置する前に、アプリケーションが使用するエンティティに、呼び出し元に依存する参照または 1 部構成の名前のみを使用するエンティティへの参照が含まれているかどうかを確認できます。このような参照はプログラミングが不十分であることを示しており、アプリケーションが配置されたときに予期しない動作が発生する可能性があります。これは、参照先エンティティの解決 (バインド) が呼び出し元のスキーマに依存しているために、この情報が実行時まで特定されないことが原因です。このような参照が検出されたら、schema_name.object_name のような適切なマルチパート名を指定することでクエリを訂正できます。

SQL の依存関係の詳細については、「SQL の依存関係について」を参照してください。

システムビューとシステム関数を使用した依存関係のレポート

SQL の依存関係を表示するために、SQL Server 2008 には sys.sql_expression_dependencies カタログビューと sys.dm_sql_referenced_entities および sys.dm_sql_referencing_entities という動的管理関数が用意されています。これらのオブジェクトに対してクエリを実行することで、ユーザー定義エンティティに関する依存関係情報を取得できます。

SQL 依存関係は、SQL Server Management Studio の [依存関係の表示] を使用して表示することもできます。詳細については、「SQL の依存関係を表示する方法 (SQL Server Management Studio)」を参照してください。

sys.sql_expression_dependencies カタログビューの使用

sys.sql_expression_dependencies カタログビューでは、データベース所有者またはデータベース管理者が、特定のデータベースに関する依存関係情報を参照できます。このビューを使用して、以下を確認することができます。

データベースにどのようなサーバー間またはデータベース間の依存関係があるか。
データベース内にどのような依存関係が存在するか。
データベースのどのエンティティに呼び出し元に依存する参照があるか。
データベースのエンティティに依存しているサーバーレベルまたはデータベースレベルの DDL トリガはどれか。
データベース内のどのモジュールがユーザー定義型 (UDT) を使用しているか。

sys.sql_expression_dependencies には次のような制限があります。

複数サーバーまたは複数データベースにまたがるエンティティの依存関係が返されるのは、有効な 4 部または 3 部構成の名前が指定されている場合に限られます。参照先エンティティの ID は返されません。
列レベルの依存関係は、スキーマバインドエンティティについてのみレポートされます。

sys.dm_sql_referenced_entities 動的管理関数の使用

sys.dm_sql_referenced_entities 関数は、指定された参照元エンティティの定義の中で名前により参照されている各ユーザー定義エンティティについて、対応する 1 行を返します。参照元エンティティは、ユーザー定義オブジェクト、サーバーレベルの DDL トリガ、またはデータベースレベルの DDL トリガのいずれかです。この情報は sys.sql_expression_dependencies によって返される情報と同じですが、結果セットは指定した参照元エンティティによって参照されるエンティティに限定されます。この関数は、開発者が自分の所有するモジュールまたは VIEW DEFINITION 権限を持っているモジュールの依存関係を追跡する場合に役立ちます。

sys.dm_sql_referencing_entities 動的管理関数の使用

sys.dm_sql_referencing_entities 関数は、現在のデータベース内で、別のユーザー定義エンティティを名前で参照する各ユーザー定義エンティティについて、対応する 1 行を返します。参照元エンティティは、ユーザー定義オブジェクト、型 (別名または CLR UDT)、XML スキーマコレクション、またはパーティション関数のいずれかです。この関数は、開発者が自分の所有するエンティティの依存関係を追跡する場合に役立ちます。たとえば、ユーザー定義型を変更する前に、この関数を使用してデータベース内でその型に依存するすべてのエンティティを確認できます。テーブル内のユーザー定義型への参照は、その型が、計算列の定義、CHECK 制約、または DEFAULT 制約で指定されていない限りレポートされないので注意してください。

例

次の例では、sys.sql_expression_dependencies カタログビューおよび sys.dm_sql_referenced_entities と sys.dm_sql_referencing_entities の 2 つの動的管理関数を使用して SQL の依存関係を取得します。

指定のエンティティが依存するエンティティのレポート

sys.sql_expression_dependencies カタログビューまたは sys.dm_sql_referenced_entities 動的管理関数に対してクエリを実行することで、指定したエンティティが依存するエンティティの一覧を取得できます。たとえば、ストアドプロシージャやトリガなどのモジュールによって参照されているエンティティの一覧を取得できます。

次の例では、テーブル、ビュー、および 3 つのストアドプロシージャを作成します。作成したオブジェクトは、この後のクエリで依存関係情報をレポートする方法を示す際に使用されます。MyView と MyProc3 の両方が Mytable を参照していることを確認してください。MyProc1 は MyView を参照し、MyProc2 は MyProc1 を参照しています。

USE AdventureWorks;
GO
-- Create entities
CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32));
GO
CREATE VIEW dbo.MyView
AS SELECT c1, c2 FROM dbo.MyTable;
GO
CREATE PROC dbo.MyProc1
AS SELECT c1 FROM dbo.MyView;
GO
CREATE PROC dbo.MyProc2
AS EXEC dbo.MyProc1;
GO
CREATE PROC dbo.MyProc3
AS SELECT * FROM AdventureWorks.dbo.MyTable;
   EXEC dbo.MyProc2;
GO

次の例では、sys.sql_expression_dependencies カタログビューに対してクエリを実行することで、MyProc3 によって参照されているエンティティを取得します。

USE AdventureWorks;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,referenced_server_name AS server_name
    ,referenced_database_name AS database_name
    ,referenced_schema_name AS schema_name
    , referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3');
GO

以下に結果セットを示します。

referencing_entity server_name database_name schema_name referenced_entity

------------------ ----------- ------------- ----------- -----------------

MyProc3 NULL NULL dbo MyProc2

MyProc3 NULL AdventureWorks dbo MyTable

(2 row(s) affected)

MyProc3 の定義の中で名前によって参照されている 2 つのエンティティが返されます。サーバー名が NULL になっているのは、参照先エンティティが有効な 4 部構成の名前を使用して指定されていないためです。MyTable にデータベース名が示されているのは、このエンティティがプロシージャ内で有効な 3 部構成の名前を使用して定義されていたためです。

sys.dm_sql_referenced_entities を使用した場合も同様の情報が返されます。この関数を使用すると、オブジェクト名のレポートに加えて、スキーマバインドエンティティと非スキーマバインドエンティティの両方について列レベルの依存関係が返されます。次の例では、MyProc3 が依存するエンティティを、列レベルの依存関係も含めて取得します。

USE AdventureWorks;
GO
SELECT referenced_server_name AS server
    , referenced_database_name AS database_name
    , referenced_schema_name AS schema_name
    , referenced_entity_name AS referenced_entity
    , referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.MyProc3', 'OBJECT');
GO

以下に結果セットを示します。

server_name database_name schema_name referenced_entity column_name

----------- ----------------- ----------- ----------------- -----------

NULL NULL dbo MyProc2 NULL

NULL AdventureWorks dbo MyTable NULL

NULL AdventureWorks dbo MyTable c1

NULL AdventureWorks dbo MyTable c2

(4 row(s) affected)

この結果セットには、同じ 2 つのエンティティに加えて、MyTable 内の c1 列と c2 列の依存関係を示す 2 行が返されます。MyProc3 の定義で、SELECT * ステートメントを使用して MyTable 内の列が参照されていることに注意してください。このようなコーディングは推奨されませんが、列レベルの依存関係は引き続きデータベースエンジンによって追跡されます。

ここまでの例では、エンティティが直接的に依存するエンティティを取得する方法を説明しました。次の例では、再帰共通テーブル式 (CTE) を使用して、エンティティの直接的および間接的な依存関係をすべて取得します。

DECLARE @referencing_entity AS sysname;
SET @referencing_entity = N'MyProc3';

WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level)
AS (
    SELECT entity_name = 
       CASE referencing_class
          WHEN 1 THEN OBJECT_NAME(referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,referenced_schema_name
    ,referenced_entity_name
    ,referenced_id
    ,0 AS level 
    FROM sys.sql_expression_dependencies AS sed 
    WHERE OBJECT_NAME(referencing_id) = @referencing_entity 
UNION ALL
    SELECT entity_name = 
       CASE sed.referencing_class
          WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,sed.referenced_schema_name
    ,sed.referenced_entity_name
    ,sed.referenced_id
    ,level + 1   
    FROM ObjectDepends AS o
    JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id
    )
SELECT entity_name,referenced_schema, referenced_entity, level
FROM ObjectDepends
ORDER BY level;
GO

以下に結果セットを示します。

entity_name referenced_schema referenced_entity level

----------- ----------------- ----------------- -----

MyProc3 dbo MyProc2 0

MyProc3 dbo MyTable 0

MyProc2 dbo MyProc1 1

MyProc1 dbo MyView 2

MyView dbo MyTable 3

(5 row(s) affected)

この結果セットには、MyProc2 および MyTable が、レベル 0 の値で示される直接的な依存関係として返されます。3 行目は、MyProc2 の定義で参照されている MyProc1 の間接的な依存関係を示しています。4 行目は MyProc1 の定義で参照されている MyView の依存関係を示し、最後の行は MyView の定義で参照されている MyTable の依存関係を示しています。

階層型の依存関係情報を取得することで、特定のエンティティの直接的および間接的な依存関係をすべて確認し、これらのオブジェクトを別のデータベースに移動する必要が生じた場合にその適切な配置順序を推測できます。

次の例では、sys.dm_sql_referenced_entities 関数を使用して同じ階層型依存関係情報を取得します。MyProc3 が依存するエンティティが、列レベルの依存関係も含めて返されます。

USE AdventureWorks;
GO
DECLARE @entity AS sysname , @type AS sysname;
SET @entity = N'dbo.MyProc3';
SET @type = N'OBJECT';

WITH ObjectDepends(referenced_schema_name, referenced_entity_name, referenced_column, 
     referenced_id,level)
AS (
    SELECT 
     referenced_schema_name
    ,referenced_entity_name
    ,referenced_minor_name AS referenced_column
    ,referenced_id
    ,0 AS level 
    FROM sys.dm_sql_referenced_entities (@entity, @type)
    UNION ALL
    SELECT
     re.referenced_schema_name
    ,re.referenced_entity_name
    ,re.referenced_minor_name AS referenced_column
    ,re.referenced_id
    ,level + 1 
    FROM ObjectDepends AS o
    CROSS APPLY sys.dm_sql_referenced_entities (o.referenced_schema_name + N'.' + o.referenced_entity_name, @type) AS re
    )
SELECT referenced_schema_name, referenced_entity_name, referenced_column, level
FROM ObjectDepends
ORDER BY level;
GO

指定のエンティティに依存するエンティティのレポート

sys.sql_expression_dependencies カタログビューまたは sys.dm_sql_referencing_entities 動的管理関数に対してクエリを実行することで、指定したエンティティに依存するエンティティの一覧を取得できます。たとえば、指定したエンティティがテーブルである場合は、自身の定義の中でそのテーブルを名前で参照するすべてのエンティティが返されます。

次の例では、エンティティ dbo.MyTable を参照するエンティティを取得します。

USE AdventureWorks;
GO
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_column, 
    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,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
WHERE referenced_id = OBJECT_ID(N'dbo.MyTable');
GO

sys.dm_sql_referenced_entities 動的管理関数を使用した場合も同様の情報が返されます。

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.MyTable', 'OBJECT');
GO

列レベルの依存関係のレポート

スキーマバインドエンティティと非スキーマバインドエンティティの両方に対し、sys.dm_sql_referenced_entities を使用して列レベルの依存関係をレポートできます。sys.sql_expression_dependencies を使用して、スキーマバインドエンティティの列レベルの依存関係をレポートすることもできます。

次の例では、sys.dm_sql_referenced_entities に対してクエリを実行することで、非スキーマバインドエンティティの列レベルの依存関係をレポートします。この例では、まず、Table1 と Table 2 およびストアドプロシージャ Proc1 を作成します。このプロシージャでは、Table1 の列 b と列 c、Table2 の列 c2 を参照します。ビュー sys.dm_sql_referenced_entities は、参照元エンティティとして指定されたストアドプロシージャで実行されます。結果セットには、参照先エンティティの Table1 と Table2 に対応する行、およびストアドプロシージャの定義の中で参照された列に対応する行が含まれます。テーブルを参照する行の column_name 列では NULL が返されます。

USE AdventureWorks;
GO
CREATE TABLE dbo.Table1 (a int, b int, c int);
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
    SELECT b, c FROM dbo.Table1;
    SELECT c2 FROM dbo.Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO

以下に結果セットを示します。

referenced_id, table_name, column_name

------------- ----------- -------------

151671588 Table1 NULL

151671588 Table1 b

151671588 Table1 c

2707154552 Table2 NULL

2707154552 Table2 c2

サーバー間およびデータベース間の依存関係のレポート

データベース間の依存関係は、エンティティが有効な 3 部構成の名前を使用して別のエンティティへの参照を作成すると作成されます。サーバー間の参照は、エンティティが有効な 4 部構成の名前を使用して別のエンティティへの参照を作成すると作成されます。サーバーとデータベースの名前は、その名前が明示的に指定された場合にのみ記録されます。たとえば、MyServer.MyDB.MySchema.MyTable と指定された場合はサーバー名とデータベース名が記録されますが、MyServer..MySchema.MyTable と指定された場合はサーバー名のみが記録されます。サーバー間およびデータベース間の依存関係を追跡する方法の詳細については、「SQL の依存関係について」を参照してください。

データベース間およびサーバー間の依存関係は、sys.sql_expression_dependencies または sys.dm_sql_referenced_entitites を使用してレポートできます。

次の例では、データベース間の依存関係をすべて取得します。この例ではまず、データベース db1 を作成し、データベース db2 と db3 のテーブルを参照する 2 つのストアドプロシージャを作成します。次に、sys.sql_expression_dependencies テーブルに対してクエリを実行して、プロシージャとテーブルの間のデータベース間依存関係をレポートします。参照先エンティティ t3 の referenced_schema_name 列に NULL が返されることに注意してください。これは、プロシージャの定義の中でそのエンティティにスキーマ名が指定されていないためです。

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

呼び出し元に依存する参照のレポート

呼び出し元に依存する参照とは、参照先エンティティのスキーマバインドが実行時に行われるために、エンティティ ID の解決が呼び出し元の既定のスキーマに依存することを意味します。これは、一般に動的スキーマバインドと呼ばれ、参照先エンティティが、スキーマ名を指定せずに EXECUTE ステートメントで呼び出されるストアドプロシージャ、拡張ストアドプロシージャ、または非スキーマバインドユーザー定義関数である場合に発生します。たとえば、EXECUTE MySchema.MyProc 形式のエンティティ参照は呼び出し元に依存しませんが、EXECUTE MyProc 形式の参照は呼び出し元に依存します。

呼び出し元に依存する参照は、参照元のモジュールが実行されたときに予期しない動作を引き起こす場合があります。たとえば、1 部構成の名前を使用してプロシージャを参照する次のストアドプロシージャがあるとします。

CREATE PROCEDURE dbo.Proc1
AS EXECUTE dbo.Proc2;

Proc1 が実行されると、Proc2 が呼び出し元のスキーマにバインドされます。Proc1 が、S1 の既定のスキーマを持つ User1 によって実行され、さらに S2 の既定のスキーマを持つ User2 によって実行されるとします。User1 によって Proc1 が実行されると、参照先エンティティが S1.Proc2 に解決されます。User2 によって Proc1 が実行されると、参照先エンティティが S2.Proc2 に解決されます。この動作のために、Proc2 の ID は Proc1 が実行されるまで解決できません。したがって、sys.sql_expression_dependencies ビューおよび sys.dm_sql_referenced_entities 関数で is_caller_dependent 列が 1 に設定されます。Proc1 が実行されると、データベースエンジンによって、呼び出し元の既定のスキーマで参照先エンティティ Proc2 が検索されます。このエンティティが見つからない場合は、dbo スキーマが調べられます。dbo スキーマで Proc2 が見つからない場合は、Proc2 の ID が解決されず、ステートメントが失敗します。アプリケーションエラーを回避するために、データベースを参照するときは 2 部構成の名前を指定することをお勧めします。

次の例では、現在のデータベース内で、呼び出し元に依存する参照を含んでいる各エンティティを取得します。

SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referenced_database_name, 
    referenced_schema_name, referenced_entity_name, referenced_id 
FROM sys.sql_expression_dependencies
WHERE is_caller_dependent = 1;

指定の UDT を使用するエンティティのレポート

次の例では、現在のデータベース内で、指定した型をその定義の中で参照している各エンティティを取得します。結果セットには、この型を使用する 2 つのストアドプロシージャが示されます。この型は HumanResources.Employee テーブル内の複数の列の定義でも使用されていますが、テーブル内の計算列、CHECK 制約、または DEFAULT 制約の定義にはこの型がないため、テーブルについては行が返されません。

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.Flag', 'TYPE');
GO

サーバーレベルの DDL トリガの依存関係のレポート

コンテキストが master データベースに設定されている場合に限り、sys.sql_expression_dependencies および sys.dm_sql_referencing_entities を使用してサーバーレベルの DDL トリガの依存関係をレポートできます。sys.dm_sql_referenced_entities 関数を使用する場合は、コンテキストはどのデータベースでもかまいません。

次の例では、sys.sql_expression_dependencies ビューに対してクエリを実行することで、サーバーレベルの DDL トリガの依存関係をレポートします。

USE master;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referencing_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_id
FROM sys.sql_expression_dependencies
WHERE referencing_class = 13;

次の方法で共有

SQL 依存関係のレポート

システムビューとシステム関数を使用した依存関係のレポート

sys.sql_expression_dependencies カタログビューの使用

sys.dm_sql_referenced_entities 動的管理関数の使用

sys.dm_sql_referencing_entities 動的管理関数の使用

例

指定のエンティティが依存するエンティティのレポート

指定のエンティティに依存するエンティティのレポート

列レベルの依存関係のレポート

サーバー間およびデータベース間の依存関係のレポート

呼び出し元に依存する参照のレポート

指定の UDT を使用するエンティティのレポート

サーバーレベルの DDL トリガの依存関係のレポート

関連項目

タスク

参照

概念

その他のリソース

次の方法で共有

SQL 依存関係のレポート

システム ビューとシステム関数を使用した依存関係のレポート

sys.sql_expression_dependencies カタログ ビューの使用

sys.dm_sql_referenced_entities 動的管理関数の使用

sys.dm_sql_referencing_entities 動的管理関数の使用

例

指定のエンティティが依存するエンティティのレポート

指定のエンティティに依存するエンティティのレポート

列レベルの依存関係のレポート

サーバー間およびデータベース間の依存関係のレポート

呼び出し元に依存する参照のレポート

指定の UDT を使用するエンティティのレポート

サーバー レベルの DDL トリガの依存関係のレポート

関連項目

タスク

参照

概念

その他のリソース

システムビューとシステム関数を使用した依存関係のレポート

sys.sql_expression_dependencies カタログビューの使用

サーバーレベルの DDL トリガの依存関係のレポート