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

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

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

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

概要

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

　例えば、パラメーターとしてテーブルを引数とすることで、そのテーブルを参照するビューやストアドプロシージャ、ユーザー定義関数などの一覧を参照元エンティティとして出力できます。

• sys.dm_sql_referencing_entities（Transact-sql）

構文と引数

構文　sys.dm_sql_referencing_entities ( '[schema_name.] referenced_entity_name','referenced_class' )

引数名	データ型	説明
[schema_name.] referenced_entity_name	nvarchar（517）	参照先エンティティの名前 参照先クラスがPARTITION_FUNCTION以外の場合、schema_nameは必須
referenced_class	nvarchar（60）	参照先エンティティのクラス 下記のいずれかの値 　OBJECT 　TYPE 　XML_SCHEMA_COLLECTION 　PARTITION_FUNCTION 1つのクラスのみ指定できる

出力内容

列名	データ型	説明
referencing_schema_name	sysname	参照元エンティティが属しているスキーマ データベースレベルおよびサーバレベルのDDLトリガーの場合はNULL
referencing_entity_name	sysname	参照元エンティティの名前
referencing_id	int	参照元エンティティのID
referencing_class	tinyint	参照元エンティティのクラス 「1」＝OBJECT 「12」＝DATABASE_DDL_TRIGGER 「13」＝SERVER_DDL_TRIGGER
referencing_class_desc	nvarchar（60）	参照元エンティティのクラスの説明
is_caller_dependent	bit	参照先エンティティが呼び出し元のスキーマに依存するため、参照先エンティティIDの解決が実行時に行われることを示す 「1」＝参照元エンティティは、エンティティを参照する可能性がある。ただし、参照先エンティティIDの解決は呼び出し元に依存しているため、特定できない 「0」＝参照先エンティティは呼び出し元に依存しない

動作例

　テーブルを参照するストアドプロシージャを、2つ作成しました。2つのストアドプロシージャは、SELECTステートメントの参照テーブルにおけるスキーマ指定の有無が異なります（図1）。

図1　参照テーブルのスキーマ指定の有無が異なる2つのストアドプロシージャを作成したところ

　ストアドプロシージャが参照するテーブルを引数に指定して、「sys.dm_sql_referencing_entities」動的管理関数を出力しました。特に、スキーマの有無などに関係なく、引数で指定したテーブルを参照するストアドプロシージャの情報が出力されました（図2）。

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

　次に、ユーザー定義タイプでも試してみます。作成したユーザー定義タイプを使用するテーブルと、ストアドプロシージャを作成しました（図3）。

図3　ユーザー定義タイプを使用するテーブルとストアドプロシージャを定義したところ

　テーブルとストアドプロシージャが参照するユーザー定義タイプを引数に指定して「sys.dm_sql_referencing_entities」動的管理関数を出力しました（図4）。

図4　ユーザー定義タイプを引数に「sys.dm_sql_referencing_entities」動的管理関数を出力したところ

　ストアドプロシージャの情報は出力されましたが、テーブルの情報は出力されませんでした。Microsoftの「sys.dm_sql_referencing_entities」のドキュメントによると、テーブルは参照元エンティティとして追跡される条件があり、ユーザー定義型の使用だけでは追跡される条件に該当しないため、出力されなかったようです。

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

筆者紹介

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

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

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

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