sys.dm_sql_referenced_entities (Transact-SQL)
指定された参照元エンティティの定義の中で、名前により参照された各ユーザー定義エンティティについて、対応する 1 行を返します。 2 つのエンティティ間の依存関係は、一方のユーザー定義エンティティ (参照先エンティティ) の名前が、もう一方のユーザー定義エンティティ (参照元エンティティ) の保存されている SQL 式の中で参照された場合に形成されます。 たとえば、参照元エンティティとしてストアド プロシージャを指定した場合、この関数は、そのストアド プロシージャの中で参照されたすべてのユーザー定義エンティティ (テーブル、ビュー、ユーザー定義型 (UDT)、他のストアド プロシージャなど) を返します。
この動的管理関数に参照元エンティティを指定すると、そのエンティティによって参照された次の種類のエンティティをレポートできます。
スキーマ バインド エンティティ
非スキーマ バインド エンティティ
複数のデータベースやサーバーにまたがるエンティティ
スキーマ バインド エンティティおよび非スキーマ バインド エンティティの列レベルの依存関係
ユーザー定義型 (別名および CLR UDT)
XML スキーマ コレクション
パーティション関数
構文
sys.dm_sql_referenced_entities (
' [ schema_name. ] referencing_entity_name ' , ' <referencing_class> ' )
<referencing_class> ::=
{
OBJECT
| DATABASE_DDL_TRIGGER
| SERVER_DDL_TRIGGER
}
引数
[ schema_name.] referencing_entity_name
参照元エンティティの名前です。 参照元のクラスが OBJECT の場合、schema_name は必須です。schema_name.referencing_entity_name のデータ型は nvarchar(517) です。
<referencing_class> ::= { OBJECT | DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER }
指定された参照元エンティティのクラスです。 クラスは 1 つのステートメントに 1 つだけ指定できます。<referencing_class> のデータ型は nvarchar(60) です。
返されるテーブル
列名 |
データ型 |
説明 |
|---|---|---|
referencing_minor_id |
int |
参照元エンティティが列の場合は列 ID。それ以外の場合は 0。 NULL 値は許可されません。 |
referenced_server_name |
sysname |
参照先エンティティのサーバー名。 有効な 4 部構成の名前を指定することによって作成されたサーバー間依存関係については、この列に値が格納されます。 マルチパート名の詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。 4 部構成の名前を指定せずにエンティティが参照される非スキーマ バインド依存関係の場合は NULL。 スキーマ バインド エンティティの場合は NULL。これらは同じデータベースに存在するため、2 部構成 (schema.object) の名前だけで定義できます。 |
referenced_database_name |
sysname |
参照先エンティティのデータベース名。 有効な 3 部構成または 4 部構成の名前を指定することによって作成された複数データベースまたは複数サーバーにまたがる参照については、この列に値が格納されます。 1 部構成または 2 部構成の名前を使って指定された非スキーマ バインド参照の場合は NULL。 スキーマ バインド エンティティの場合は NULL。これらは同じデータベースに存在するため、2 部構成 (schema.object) の名前だけで定義できます。 |
referenced_schema_name |
sysname |
参照先エンティティが属しているスキーマ。 スキーマ名を指定せずにエンティティが参照される非スキーマ バインド参照の場合は NULL。 スキーマ バインド参照の場合、NULL にすることはできません。 |
referenced_entity_name |
sysname |
参照先エンティティの名前。 NULL 値は許可されません。 |
referenced_minor_name |
sysname |
参照先エンティティが列の場合は列名。それ以外の場合は NULL。 たとえば、参照先エンティティ自体を一覧表示する行では、referenced_minor_name は NULL になります。 参照元エンティティの中で列が名前で指定されていた場合、または SELECT * ステートメントの中で親エンティティが使用されていた場合、参照先エンティティは列になります。 |
referenced_id |
int |
参照先エンティティの ID。 referenced_minor_id が 0 以外の場合、referenced_id は、その列が定義されているエンティティになります。 複数サーバーにまたがる参照の場合は常に NULL。 複数データベースにまたがる参照で、データベースがオフラインか、エンティティをバインドできないために ID を判別できない場合は NULL。 データベース内の参照で ID を判別できない場合は、NULL。 非スキーマ バインド参照では、次の場合に ID を解決できません。
スキーマ バインド参照の場合、NULL にすることはできません。 |
referenced_minor_id |
int |
参照先エンティティが列の場合は列 ID。それ以外の場合は 0。 たとえば、参照先エンティティ自体を一覧表示する行では、referenced_minor_is は 0 になります。 非スキーマ バインド参照の場合、列の依存関係は、すべての参照先エンティティがバインドできる場合にのみ報告されます。 バインドできない参照先エンティティが 1 つでも存在した場合、列レベルの依存関係は報告されず、referenced_minor_id は 0 になります。 例 D を参照してください。 |
referenced_class |
tinyint |
参照先エンティティのクラス。 1 = オブジェクトまたは列 6 = 型 10 = XML スキーマ コレクション 21 = パーティション関数 |
referenced_class_desc |
nvarchar(60) |
参照先エンティティのクラスの説明。 OBJECT_OR_COLUMN TYPE XML_SCHEMA_COLLECTION PARTITION_FUNCTION |
is_caller_dependent |
bit |
参照先エンティティのスキーマ バインドが実行時に行われるため、エンティティ ID の解決が呼び出し元のスキーマに依存することを示します。 これが該当するのは、参照先エンティティがストアド プロシージャ、拡張ストアド プロシージャ、または、EXECUTE ステートメント内で呼び出されるユーザー定義関数である場合です。 1 = 参照先エンティティが呼び出し元に依存し、実行時に解決されます。 この場合、referenced_id は NULL です。 0 = 参照先エンティティの ID は呼び出し元に依存しません。 スキーマ バインド参照のほか、スキーマ名を明示的に指定するデータベース間参照やサーバー間参照の場合は常に 0 になります。 たとえば、EXEC MyDatabase.MySchema.MyProc 形式のエンティティ参照は呼び出し元に依存しません。 ただし、EXEC MyDatabase..MyProc 形式の参照は呼び出し元に依存します。 |
is_ambiguous |
bit |
参照があいまいであり、実行時には、ユーザー定義関数、ユーザー定義型 (UDT)、または xml 型の列への xquery 参照に解決される可能性があることを示します。 たとえば、ストアド プロシージャ内で SELECT Sales.GetOrder() FROM Sales.MySales というステートメントが定義されているとします。 Sales.GetOrder() が Sales スキーマ内のユーザー定義関数なのか、GetOrder() という名前のメソッドを持つ UDT 型の Sales という名前の列なのかは、ストアド プロシージャが実行されるまで不明です。 1 = ユーザー定義関数の参照なのか、列のユーザー定義型 (UDT) のメソッドなのかがあいまいです。 0 = 参照は明確です。つまり、関数を呼び出したときに、エンティティを正しくバインドできます。 スキーマ バインド参照の場合は常に 0 になります。 |
is_selected |
bit |
1 = オブジェクトまたは列が選択されています。 |
is_updated |
bit |
1 = オブジェクトまたは列が変更されています。 |
is_select_all |
bit |
1 = オブジェクトは SELECT * 句で使用されています (オブジェクトレベルのみ)。 |
is_all_columns_found |
bit |
1 = オブジェクトに対するすべての列の依存関係が見つかりました。 0 = オブジェクトに対する列の依存関係が見つかりませんでした。 |
例外
次のいずれかの条件に該当した場合は、空の結果セットが返されます。
システム オブジェクトが指定されている。
指定されたエンティティが現在のデータベースに存在しない。
指定されたエンティティがいずれのエンティティも参照しない。
無効なパラメーターが渡される。
指定された参照元エンティティが番号付きストアド プロシージャの場合は、エラーが返されます。
列の依存関係を解決できない場合は、エラー 2020 が返されます。 このエラーによって、クエリからオブジェクト レベルの依存関係が返されなくなることはありません。
説明
この関数は、サーバー レベルの DDL トリガーを参照するエンティティを取得するために、任意のデータベースのコンテキストで実行できます。
次の表に、依存関係情報が作成および管理されるエンティティの種類を示します。 ルール、既定値、一時テーブル、一時ストアド プロシージャ、またはシステム オブジェクトについては、依存関係情報は作成も管理もされません。
エンティティの種類 |
参照元エンティティ |
参照先エンティティ |
|---|---|---|
テーブル |
可* |
可 |
ビュー |
可 |
可 |
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 より大きな整数値を持つ番号付きストアド プロシージャは、参照元エンティティとしても、参照先エンティティとしても追跡されません。
権限
sys.dm_sql_referenced_entities に対する SELECT 権限および参照元エンティティに対する VIEW DEFINITION 権限が必要です。 既定では、SELECT 権限が public に与えられます。 参照元エンティティがデータベース レベルの DDL トリガーである場合は、データベースに対する VIEW DEFINITION 権限またはデータベースに対する ALTER DATABASE DDL TRIGGER 権限が必要です。 参照元エンティティがサーバー レベルの DDL トリガーである場合は、サーバーに対する VIEW ANY DEFINITION 権限が必要です。
使用例
A. データベース レベルの DDL トリガーによって参照されるエンティティを取得する
次の例では、データベース レベルの DDL トリガー ddlDatabaseTriggerLog によって参照されるエンティティ (テーブルおよび列) を取得します。
USE AdventureWorks2012;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');
GO
B. オブジェクトによって参照されるエンティティを取得する
次の例では、ユーザー定義関数 dbo.ufnGetContactInformation によって参照されるエンティティを取得します。
USE AdventureWorks2012;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');
GO
C. 列の依存関係を取得する
次の例では、a 列と b 列の合計として定義された計算列 c を持つテーブル Table1 を作成します。 その後、sys.dm_sql_referenced_entities ビューが呼び出されます。 このビューは、2 つの行 (計算列で定義された各列につき 1 行) を返します。
USE AdventureWorks2012;
GO
CREATE TABLE dbo.Table1 (a int, b int, c AS a + b);
GO
SELECT referenced_schema_name AS schema_name,
referenced_entity_name AS table_name,
referenced_minor_name AS referenced_column,
COALESCE(COL_NAME(OBJECT_ID(N'dbo.Table1'),referencing_minor_id), 'N/A') AS referencing_column_name
FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT');
GO
-- Remove the table.
DROP TABLE dbo.Table1;
GO
以下に結果セットを示します。
schema_name table_name referenced_column referencing_column
----------- ---------- ----------------- ------------------
dbo Table1 a c
dbo Table1 b c
D. 非スキーマ バインド列の依存関係を取得する
次の例では、Table1 を削除し、Table2 およびストアド プロシージャ Proc1 を作成します。 このプロシージャは、Table2 および存在しないテーブル Table1 を参照します。 ビュー sys.dm_sql_referenced_entities は、参照元エンティティとして指定されたストアド プロシージャで実行されます。 結果セットには、Table1 に対する 1 行と Table2 に対する 3 行があります。 Table1 は存在しないので、列の依存関係が解決されず、エラー 2020 が返されます。 is_all_columns_found 列の Table1 に対する 0 は、検出できなかった列があることを示します。
USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'dbo.Table1', 'U' ) IS NOT NULL
DROP TABLE dbo.Table1;
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
SELECT a, b, c FROM Table1;
SELECT c1, c2 FROM Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS referenced_column_name, is_all_columns_found
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO
以下に結果セットを示します。
referenced_id table_name referenced_column_name is_all_columns_found
------------- ------------ ----------------------- --------------------
935674381 Table2 NULL 1
935674381 Table2 C1 1
935674381 Table2 C2 1
NULL Table1 NULL 0
Msg 2020, Level 16, State 1, Line 1The dependencies reported for entity "dbo.Proc1" might not include references to all 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.
E. 依存関係の動的管理を行う
次の例は、例 D を一歩進めて、依存関係を動的に管理する方法を示しています。 この例では、まず、例 D で削除した Table1 を作成し直しています。 次に、参照元エンティティとして指定されたストアド プロシージャで sys.dm_sql_referenced_entities を再度実行します。 結果セットを見ると、両方のテーブルのほか、ストアド プロシージャ内で定義された各列が返されていることがわかります。 さらに、is_all_columns_found 列ではすべてのオブジェクトと列に 1 が返されます。
USE AdventureWorks2012;
GO
CREATE TABLE Table1 (a int, b int, c AS a + b);
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name as column_name, is_all_columns_found
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO
DROP TABLE Table1, Table2;
DROP PROC Proc1;
GO
以下に結果セットを示します。
referenced_id table_name referenced_column_name is_all_columns_found
------------- ------------ ----------------------- --------------------
935674381 Table2 NULL 1
935674381 Table2 c1 1
935674381 Table2 c2 1
967674495 Table1 NULL 1
967674495 Table1 a 1
967674495 Table1 b 1
967674495 Table1 c 1
F. オブジェクトまたは列の使用状況を返す
次の例では、ストアド プロシージャ HumanResources.uspUpdateEmployeePersonalInfo のオブジェクトと列の依存関係を返します。 このプロシージャは、指定された BusinessEntityID の値に基づいて、Employee テーブルの列 NationalIDNumber、BirthDate,、MaritalStatus、および Gender を更新します。 TRY…CATCH ブロック内で定義されているもう 1 つのストアド プロシージャ upsLogError は、実行エラーをキャプチャします。 is_selected、is_updated、および is_select_all 列では、参照元オブジェクト内でのこれらのオブジェクトと列の使用方法についての情報が返されます。 変更されているテーブルと列は、is_updated 列で 1 と示されます。 BusinessEntityID 列は選択されているのみで、ストアド プロシージャ uspLogError は選択も変更もされていません。
SELECT referenced_entity_name AS table_name, referenced_minor_name as column_name, is_selected, is_updated, is_select_all
FROM sys.dm_sql_referenced_entities ('HumanResources.uspUpdateEmployeePersonalInfo', 'OBJECT');
以下に結果セットを示します。
table_name column_name is_selected is_updated is_select_all
------------- ------------------- ----------- ---------- -------------
uspLogError NULL 0 0 0
Employee NULL 0 1 0
Employee BusinessEntityID 1 0 0
Employee NationalIDNumber 0 1 0
Employee BirthDate 0 1 0
Employee MaritalStatus 0 1 0
Employee Gender 0 1 0