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

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

» 2022年09月05日 05時00分 公開
[伊東敏章@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 ( '[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 図1 作成したストアドプロシージャの定義
図2 図2 ストアドプロシージャを引数に「sys.dm_sql_referenced_entities」動的管理関数を出力したところ

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

図3 図3 スキーマを指定せずテーブルを参照するストアドプロシージャを作成したところ
図4 図4 「sys.dm_sql_referenced_entities」動的管理関数を出力したところ

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

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

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

図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回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。


Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

注目のテーマ

Microsoft & Windows最前線2025
AI for エンジニアリング
ローコード/ノーコード セントラル by @IT - ITエンジニアがビジネスの中心で活躍する組織へ
Cloud Native Central by @IT - スケーラブルな能力を組織に
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。