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;