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 テーブル値関数については、列の依存関係は追跡されません」という記述を削除しました。