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

SQL の依存関係について

SQL の依存関係とは、SQL 式の中で名前参照を使用し、あるエンティティを別のエンティティに依存させることです。別のエンティティを参照しているエンティティを "参照元エンティティ"といいます。参照元エンティティは、その定義をシステムカタログに保存します。別のエンティティによって参照されるエンティティを "参照先エンティティ" といいます。データベースエンジンによって追跡される依存関係には、2 つの種類があります。

スキーマバインド依存関係

スキーマバインド依存関係とは、参照元エンティティが存在する限り、参照先エンティティを削除または変更することができない 2 つのエンティティ間のリレーションシップです。スキーマバインド依存関係は、WITH SCHEMABINDING 句を使用してビューまたはユーザー定義関数を作成した場合に作成されます。また、CHECK 制約、DEFAULT 制約、または計算列の定義で、テーブルが他のエンティティ (Transact-SQL ユーザー定義関数、ユーザー定義型、XML スキーマコレクションなど) を参照する場合にも、スキーマバインド依存関係が作成されます。2 つの部分 (schema_name.object_name) で構成される名前を使用してオブジェクトを指定している場合は、スキーマバインド参照として扱われません。
非スキーマバインド依存関係

非スキーマバインド依存関係とは、参照先エンティティを削除または変更できる 2 つのエンティティ間のリレーションシップです。

次の図に SQL の依存関係の例を示します。

SQL の依存関係

この図では、プロシージャ X とプロシージャ Y の 2 つのエンティティがあります。プロシージャ X の SQL 式では、プロシージャ Y を名前で参照しています。したがって、プロシージャ X は参照元エンティティ、プロシージャ Y は参照先エンティティとなります。プロシージャ X はプロシージャ Y に依存しているため、プロシージャ Y が存在しないと、プロシージャ X はランタイムエラーで失敗します。しかし、プロシージャ Y はプロシージャ X が存在しなくても失敗しません。

次の例は、ストアドプロシージャ X がストアドプロシージャ Y に依存するようすを表示します。

USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
    EXEC dbo.Y;
GO

Y に対する X の依存関係を表示するには、次のクエリを実行します。

SELECT * 
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID('X')
    AND referenced_id = OBJECT_ID('Y')
    AND referenced_schema_name = 'dbo'
    AND referenced_entity_name = 'Y'
    AND referenced_database_name IS NULL
    AND referenced_server_name IS NULL;
GO

参照元エンティティと参照先エンティティの種類

次の表に、依存関係情報が作成および管理されるエンティティの種類を示します。この表には、エンティティが参照元エンティティまたは参照先エンティティとして追跡されるかどうかが示されています。ルール、既定値、一時テーブル、一時ストアドプロシージャ、システムオブジェクトについては、依存関係情報は作成も管理もされません。

エンティティの種類	参照元エンティティ	参照先エンティティ
テーブル	可*	可
ビュー	可	可
Transact-SQL ストアドプロシージャ**	可	可
CLR ストアドプロシージャ	不可	可
Transact-SQL ユーザー定義関数	可	可
CLR ユーザー定義関数	不可	可
CLR トリガ (DML および DDL)	不可	不可
Transact-SQL DML トリガ	可	不可
Transact-SQL データベースレベルの DDL トリガ	可	不可
Transact-SQL サーバーレベルの DDL トリガ	可	不可
拡張ストアドプロシージャ	不可	可
キュー	不可	可
シノニム	不可	可
型 (別名および CLR ユーザー定義型)	不可	可
XML スキーマコレクション	不可	可
パーティション関数	不可	可

* テーブルは、Transact-SQL モジュール、ユーザー定義型、XML スキーマコレクション (計算列の定義内)、CHECK 制約、DEFAULT 制約のいずれかを参照する場合にのみ、参照元エンティティとして追跡されます。

** 1 より大きな整数値を持つ番号付きストアドプロシージャは、参照元エンティティとしても、参照先エンティティとしても追跡されません。

依存関係情報の追跡のしくみ

参照元エンティティを作成、変更、または削除すると、データベースエンジンによって依存関係情報が自動的に追跡され、その情報が SQL Server システムカタログに記録されます。たとえば、テーブルを参照するトリガを作成すると、該当するエンティティ間の依存関係が記録されます。その後、このトリガを削除すると、依存関係情報がシステムカタログから削除されます。

依存関係が ID で追跡される以前のバージョンの SQL Server とは異なり、依存関係が名前で追跡されるようになりました。つまり、参照元エンティティの作成時に参照先エンティティが存在していない場合でも、2 つのエンティティ間の依存関係情報が追跡されます。この状況は、名前の遅延解決によって発生します。たとえば、テーブルを参照するストアドプロシージャは、参照先のテーブルがデータベースに存在しない場合でも正常に作成できますが、実行することはできません。この場合、プロシージャとテーブル間の依存関係は記録されますが、まだ存在していない参照先テーブルの ID は記録されません。参照先のテーブルを後で作成すると、そのテーブルの ID が別の依存関係情報と共に返されます。

依存関係情報が追跡されるのは、参照元エンティティの保存されている SQL 式に、参照先エンティティが名前で表示されるときです。依存関係情報が取得されるのは、次の方法によってエンティティが名前で参照されるときです。

Transact-SQLモジュールの定義で次のステートメントのいずれかを使用する。
- データ操作言語 (DML) ステートメント (SELECT、INSERT、UPDATE、DELETE、MERGE)
- EXECUTE
- DECLARE
- SET (SET をユーザー定義関数またはユーザー定義型と共に使用する場合。たとえば、DECLARE @var int; SET @var = dbo.udf1)
CREATE、ALTER、DROP などのデータ定義言語 (DDL) ステートメントを使用して Transact-SQL モジュールの定義で参照されるエンティティは、追跡されません。
CREATE TABLE ステートメント、ALTER TABLE ステートメント、または DROP TABLE ステートメントを使用する (これらのステートメントが Transact-SQL モジュール内になく、参照先エンティティが、計算列、CHECK 制約、または DEFAULT 制約で定義された Transact-SQL ユーザー定義関数、ユーザー定義型、または XML スキーマコレクションの場合)。

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

エンティティが別のエンティティを参照する際、3 つの部分で構成された有効な名前を使用している場合にデータベース間依存関係が作成されます。エンティティが別のエンティティを参照する際、4 つの部分で構成された有効な名前を使用している場合にサーバー間参照が作成されます。サーバーとデータベースの名前は、その名前が明示的に指定された場合にのみ記録されます。たとえば、MyServer.MyDB.MySchema.MyTable と指定した場合はサーバー名とデータベース名が記録されますが、MyServer..MySchema.MyTable と指定した場合はサーバー名のみが記録されます。有効なマルチパート名の詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。

次の制限事項が適用されます。

OPENROWSET ステートメント、OPENQUERY ステートメント、および OPENDATASOURCE ステートメントのサーバー間の依存関係は追跡されません。
EXEC ('…') AT linked_server ステートメントの依存関係は追跡されません。

次の表は、追跡されるサーバー間およびデータベース間の依存関係、システムカタログに記録される情報、sys.sql_expression_dependencies (Transact-SQL) によってレポートされる情報をまとめたものです。

モジュール内の SQL 式	追跡の可否	参照先サーバー名	参照先データベース名	参照先スキーマ名	参照先エンティティ名
SELECT * FROM s1.db2.sales.t1	可	s1	db2	sales	t1
SELECT * FROM db3..t1	可		db3		t1
EXEC db2.dbo.Proc1	可		db2	dbo	proc1
EXEC ('…') AT linked_srv1	不可
EXEC linked_svr1.db2.sales.proc2	可	linked_svr1	db2	sales	proc2

依存関係の追跡への照合順序の影響

照合順序は、データの並べ替えおよび比較を行う規則を決定します。データベースの照合順序は、データベース内のエンティティの依存関係情報を識別するのに使用されます。たとえば、大文字と小文字が区別されるデータベースに Some_Table エンティティと SOME_TABLE エンティティが保存されており、ストアドプロシージャがこれらのエンティティを参照する場合、比較によってこれら 2 つは異なるエンティティと見なされるので、両方の依存関係情報が記録されます。データベースで大文字と小文字が区別されない照合順序が使用される場合は、1 つの依存関係のみが記録されます。

サーバー間およびデータベース間の依存関係では、参照元オブジェクトが存在するサーバーの照合順序は、サーバーとデータベースの名前を解決するのに使用されます。現在のデータベースの照合順序は、スキーマ名とオブジェクト名を解決するのに使用されます。

次のストアドプロシージャの定義を考えてみます。"大文字と小文字の区別なし" サーバー照合順序を使用する SQL Server インスタンス上で、"大文字と小文字の区別あり" 照合順序を使用するデータベースにストアドプロシージャを作成した場合、srv_referenced.db_referenced.dbo.p_referenced エンティティと srv_referenced.db_referenced.DBO.P_REFERENCED エンティティの 2 つの依存関係情報が記録されます。

CREATE PROCEDURE p_referencing AS
    EXECUTE srv_referenced.db_referenced.dbo.p_referenced
    EXECUTE srv_referenced.db_referenced.DBO.P_REFERENCED
    EXECUTE SRV_REFERENCED.DB_REFERENCED.dbo.p_referenced;

あいまいな参照の解決

参照があいまいであるのは、実行時に、ユーザー定義関数、ユーザー定義型 (UDT)、または xml 型の列への xquery 参照に解決される可能性がある場合です。

次のストアドプロシージャの定義を考えてみます。

CREATE PROCEDURE dbo.p1 AS 
    SELECT column_a, Sales.GetOrder() FROM Sales.MySales;

ストアドプロシージャが作成される時点では、Sales.GetOrder() が Sales スキーマ内の GetOrder という名前のユーザー定義関数への参照なのか、GetOrder() という名前のメソッドを持つ UDT 型の Sales という名前の列への参照なのかは不明です。参照があいまいな場合、依存関係は、sys.sql_expression_dependencies および sys.dm_sql_referenced_entities の is_ambiguous 列を 1 に設定することで、あいまいであるとしてレポートされます。次の依存関係情報がレポートされます。

ストアドプロシージャとテーブルの間の依存関係。
ストアドプロシージャとユーザー定義関数の間の依存関係。関数が存在する場合、関数の ID がレポートされます。それ以外の場合、ID は NULL です。
関数の依存関係は、あいまいとしてマークされます。つまり、is_ambiguous が 1 に設定されます。
列レベルの依存関係はレポートされません。これは、列が参照されるステートメントをバインドできないためです。

依存関係の維持

データベースエンジンは、スキーマバインド依存関係と非スキーマバインド依存関係の両方を維持します。これらの依存関係は、データベースを以前のバージョンの SQL Server からアップグレードしたり、データベースの照合順序を変更したりするときなど、依存関係の追跡に影響を与える操作の実行時に自動的に更新されます。

変更履歴

変更内容
「解説」にあった「CLR テーブル値関数については、列の依存関係は追跡されません」という記述を削除しました。

次の方法で共有