対象オブジェクトが参照する参照先エンティティの情報を出力する：SQL Server動的管理ビューレファレンス（142）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、対象オブジェクトが参照する参照先エンティティの情報を出力する方法について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_sql_referenced_entities」における、対象オブジェクトが参照する参照先エンティティの情報を出力する方法について解説します。対応バージョンは、SQL Server（サポートされている全てのバージョン）、「Azure SQL Database」「Azure SQL Managed Instance」です。

概要

　「sys.dm_sql_referenced_entities」動的管理関数では、パラメーターに対象とするオブジェクトを指定することで、そのオブジェクトが参照している別のユーザー定義オブジェクトの情報の一覧を出力することが可能です。

　例えば、ストアドプロシージャを引数として指定することで、そのストアドプロシージャが参照しているテーブル、ビュー、ユーザー定義タイプ、またはその他のストアドプロシージャなど、参照している全てのユーザー定義オブジェクトの情報を参照先エンティティとして出力します。

• sys.dm_sql_referenced_entities（Transact-SQL）

構文と引数

構文　sys.dm_sql_referenced_entities ( '[schema_name.] referencing_entity_name','referencing_class' )

引数名	データ型	説明
[schema_name.] referencing_entity_name	nvarchar（517）	参照元エンティティの名前 参照元のクラスがOBJECTの場合、schema_nameが必要
referencing_class	nvarchar（60）	参照元エンティティのクラス。下記のいずれかの値 　OBJECT 　DATABASE_DDL_TRIGGER 　SERVER_DDL_TRIGGER 1つのクラスのみを指定できる

出力内容

列名	データ型	説明
referencing_minor_id	int	参照元エンティティが列の場合は列ID。それ以外の場合は「0」
referenced_server_name	sysname	参照先エンティティのサーバ名。有効な4部構成の名前を指定して作成されたサーバ間の依存関係の場合は、値が格納される 4部構成の名前を指定せずにエンティティが作成された場合や、非スキーマバインド参照の場合はNULL
referenced_database_name	sysname	参照先エンティティのデータベース名 有効な3部構成または4部構成の名前を指定して作成された複数データベースまたは複数サーバにまたがるエンティティの場合は、値が格納される 1部構成または2部構成の名前を使用してエンティティが作成された場合や、非スキーマバインド参照の場合はNULL
referenced_schema_name	sysname	参照先エンティティが属しているスキーマ スキーマ名を指定せずにエンティティが参照される非スキーマバインド参照の場合はNULL
referenced_entity_name	sysname	参照先エンティティの名前
referenced_minor_name	sysname	参照先エンティティが列の場合は列名。それ以外の場合はNULL 参照元エンティティの中で列が名前で指定されていた場合、またはSELECT *ステートメントの中で親エンティティが使用されていた場合、参照先エンティティは列になる
referenced_id	int	参照先エンティティのID 「referenced_minor_id」が「0」以外の場合、「referenced_id」は、その列が定義されているエンティティ 複数サーバにまたがる参照の場合はNULL 複数データベースにまたがる参照で、データベースがオフラインかエンティティをバインドできないためにIDを判別できない場合はNULL スキーマバインドされていない参照の場合、参照先エンティティがデータベースに存在しない場合、または名前解決が呼び出し元に依存している場合はNULL
referenced_minor_id	int	参照先エンティティが列の場合は列ID。それ以外の場合は「0」 非スキーマバインド参照の場合、列の依存関係は全ての参照先エンティティをバインドできる場合にのみ報告される。バインドできない参照先エンティティが1つでも存在した場合、列レベルの依存関係は報告されず「0」になる
referenced_class	tinyint	参照先エンティティのクラス 「1」＝OBJECT_OR_COLUMN 「6」＝TYPE 「10」＝XML_SCHEMA_COLLECTION 「21」＝PARTITION_FUNCTION
referenced_class_desc	nvarchar（60）	参照先エンティティのクラスの説明
is_caller_dependent	bit	参照先エンティティのスキーマバインドが実行時に発生するかどうか 「1」＝参照先エンティティは呼び出し元に依存し、実行時に解決される 「0」＝参照先エンティティ ID は、呼び出し元に依存しない
is_ambiguous	bit	参照があいまいであり、実行時にユーザー定義関数、ユーザー定義型（UDT）、またはxml型の列へのxquery参照に解決できることを示す 「1」＝ユーザー定義関数または列のユーザー定義型（UDT）メソッドへの参照があいまい 「0」＝明確な参照
is_selected	bit	「1」＝オブジェクトまたは列が選択されている
is_updated	bit	「1」＝オブジェクトまたは列が変更されている
is_select_all	bit	「1」＝オブジェクトはSELECT *句で使用される（オブジェクトレベルのみ）
is_all_columns_found	bit	「1」＝オブジェクトに対する全ての列の依存関係が見つかった 「0」＝オブジェクトに対する列の依存関係は見つからなかった
is_insert_all	bit	「1」＝オブジェクトは、列リストのないINSERTステートメントで使用されている（オブジェクトレベルのみ） ※SQL Server 2016以降のみ
is_incomplete	bit	「1」＝オブジェクトまたは列にバインドエラーがあり不完全 ※SQL Server 2016 SP2以降のみ

動作例

　テーブルを参照するストアドプロシージャを作成し、引数に指定して「sys.dm_sql_referenced_entities」動的管理関数を出力しました。ストアドプロシージャが参照するテーブルの情報と、参照するテーブルの列の情報が出力されました（図1、図2）。

図1　作成したストアドプロシージャの定義

図2　ストアドプロシージャを引数に「sys.dm_sql_referenced_entities」動的管理関数を出力したところ

　次に、スキーマを指定せずにテーブルを参照するストアドプロシージャを作成し、引数に指定して「sys.dm_sql_referenced_entities」動的管理関数を出力しました。出力結果から、スキーマが指定されていないことが分かります。（図3、図4）。

図3　スキーマを指定せずテーブルを参照するストアドプロシージャを作成したところ

図4　「sys.dm_sql_referenced_entities」動的管理関数を出力したところ

　次に、列を明示的に指定してテーブルを参照するストアドプロシージャを作成し（図5）、参照する列を削除した後、このストアドプロシージャを引数に指定して「sys.dm_sql_referenced_entities」動的管理関数を出力しました。

図5　列を指定してテーブルを参照するストアドプロシージャを作成したところ

　参照する列が存在しない場合には、動的管理関数の出力の途中でエラー「207」および「2020」が出力され、実行に失敗するようです（図6）。

図6　列「COL03」を削除後にエラー「207」「2020」が出力されたところ

　「sys.dm_sql_referenced_entities」動的管理関数を使用することで、テーブルや列の参照方法や削除済みの列を参照していないかの確認にも使用できそうなため、データベースアプリケーションの開発時にも使用できそうです。

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

BIPROGY株式会社（ビプロジー）所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

BIPROGY株式会社（ビプロジー）所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。