システム開発をしている皆さん、特にOracleなど大規模で商業向けのシステム開発をしている場合、なかなかDBのドキュメントが更新されていなかったり、ER図や外部参照制約(FK)がないなど、ドキュメントが不足していて、なかなかDBの構造が見えずに、システムの理解ができないということがあると思います。
（ER図や外部参照制約くらいマイルストーンのタイミングで更新してよ、と思いますが…）
今回は、Oracleデータベースのテーブルおよびカラムのメタ情報を一括取得するためのSQLクエリを紹介したいと思います。メタ情報って、a5m2などのSQLクライアントツールを使用すれば、メタ情報って取得できますが、SQLでどのテーブルにアクセスすればDBのメタ情報が取得できるかということは、ちょっと知っておいた方が応用は効くと思います。
Oracleのメタデータとパフォーマンス情報
Oracleデータベースでは、データベース自体の情報や実行中のパフォーマンスを管理・監視するための特別なビューが用意されています。これらのビューを利用することで、データベースの設計、管理、パフォーマンスチューニングが行いやすくなります。SQL開発者が確認するということは少ないかと思いますが、インデックスの設計などはどっちかというとDB設計者よりもSQL設計者が行った方が良いと思うので、ざっくりと知っておいた方が良いかと。
静的データ・ディクショナリ・ビュー
静的データ・ディクショナリ・ビュー（Static Data Dictionary Views）は、データベースのメタデータ（データの構造や定義に関する情報）を示すビューです。これらのビューはデータベース自体によって管理され、ユーザーが直接変更することはできません。主に以下のような情報を提供します：
- テーブルの定義（列名、データ型、制約など）
- インデックスやビューの定義
- ユーザーや権限の情報
- オブジェクト間の依存関係
よく使用するというか、テーブル物理名、テーブル論理名、カラム物理名、カラム論理名など、ここら辺の情報というのは、次のようなテーブルで取得できます：
USER_TAB_COMMENTS: テーブルのコメント情報（論理名）を格納USER_COL_COMMENTS: カラムのコメント情報（論理名）を格納USER_TAB_COLUMNS: カラムの詳細情報（データ型、精度、長さ、NULL許可など）を格納
参照: ALL_TAB_COMMENTS, ALL_COL_COMMENTS, ALL_TAB_COLUMNS
動的パフォーマンス・ビュー
動的パフォーマンス・ビュー（Dynamic Performance Views）は、実行中のデータベースの動的なパフォーマンス情報を提供するビューです。これらのビューは、データベースが実行されている間に動的に生成され、システムの実行状態やパフォーマンスをモニタリングするのに役立ちます。主な情報としては以下があります：
- セッションの情報（現在のアクティブなセッション数、セッションごとのリソース使用状況）
- クエリの実行計画や実行統計
- データベースのウェイトイベント（ロック待ち、I/O待ちなど）
- リソースの消費状況（CPU使用率、メモリ使用量など）
動的パフォーマンス・ビューは、特にトラブルシューティングやパフォーマンスチューニング時に有用であり、リアルタイムでの監視や分析に適しています。
SQLクエリ
テーブルとカラムにおける物理名と論理名は、以下のシンプルなSQLで取得できます。
個人的にはこれで十分事足りることも多いですが、これをベースにいろいろ必要な情報を列に追加していくと良いと思います。
SELECT
utc.table_name AS "テーブル物理名",
utc.comments AS "テーブル論理名",
ucc.column_name AS "カラム物理名",
ucc.comments AS "カラム論理名"
FROM user_tab_comments utc
JOIN user_col_comments ucc ON utc.table_name = ucc.table_name
JOIN user_tab_columns utc2 ON ucc.table_name = utc2.table_name
AND ucc.column_name = utc2.column_name
ORDER BY utc.table_name, utc2.column_id;で、カラムIDや、必須やPKなどの属性は適宜別のテーブルを結合して、取得することになります。
SELECT
utc.table_name AS "テーブル物理名",
utc.comments AS "テーブル論理名",
LPAD(utc2.column_id, 2, '0') AS "カラムID",
ucc.column_name AS "カラム物理名",
ucc.comments AS "カラム論理名",
CASE
WHEN utc2.data_precision IS NOT NULL THEN
utc2.data_type || '(' || utc2.data_precision || ',' || utc2.data_scale || ')'
ELSE
utc2.data_type || '(' || utc2.data_length || ')'
END AS "データ型",
CASE
WHEN utc2.nullable = 'N' THEN 'Yes'
ELSE '-'
END AS "必須",
CASE
WHEN pk.column_name IS NOT NULL THEN 'Yes'
ELSE '-'
END AS "PK"
FROM user_tab_comments utc
JOIN user_col_comments ucc ON utc.table_name = ucc.table_name
JOIN user_tab_columns utc2 ON ucc.table_name = utc2.table_name
AND ucc.column_name = utc2.column_name
LEFT JOIN (
SELECT ucc.table_name, ucc.column_name
FROM user_cons_columns ucc
JOIN user_constraints uc ON ucc.constraint_name = uc.constraint_name
WHERE uc.constraint_type = 'P'
) pk ON ucc.table_name = pk.table_name AND ucc.column_name = pk.column_name
ORDER BY utc.table_name, utc2.column_id;制約情報については、user_cons_columns, user_constraints テーブルからまずPKの属性を所持するレコードのみを取得してから（導出テーブルの作成）、結合します。
クエリの説明
簡単にクエリの説明をします。
- テーブルの結合:
user_tab_comments(utc) はテーブルの物理名と論理名（コメント）を提供します。user_col_comments(ucc) はカラムの物理名と論理名（コメント）を提供します。user_tab_columns(utc2) はカラムの詳細情報（データ型、精度、長さ、NULL許可など）を提供します。
- データ型のフォーマット:
CASE文を使用して、DATA_PRECISIONが存在する場合と存在しない場合でデータ型のフォーマットを切り替えています。DATA_PRECISIONがある場合は、DATA_TYPE(DATA_PRECISION, DATA_SCALE)の形式で出力します。- それ以外の場合は、
DATA_TYPE(DATA_LENGTH)の形式で出力します。
- 必須列の判断:
NULLABLE列をチェックし、’N’（NULL不可）の場合は ‘Yes’ を、’Y’（NULL可）の場合は ‘No’ を出力します。
- ORDER BY:
- 出力結果をテーブル名とカラムの順序で整列しています。
