Oracle SQL

Oracle データベースで SQL を使ってテーブルの外部キーを確認する方法

Oracle データベースで SQL を使ってテーブルの外部キーを確認する方法

Oracle データベースのコマンドライン (SQL*Plus 等)で、あるテーブルの外部キー(どのテーブルとどのテーブルが関連しているか)を確認しようとしても DESCRIBE (DESC) コマンドでは表示されません。SQL Developer ツール等を使えば外部キーは簡単に確認することが出来ますが、SQL を使って外部キーを確認したい場合も多々あると思います。



そのような場合には、以下の SQL を使うことで様々なテーブルの外部キーを一覧表示することが出来ます。ここでは SYS や SYSTEM 等のシステム関連テーブルは除外しています。

SELECT
  FOREIGN_TABLE.OWNER AS TABLE_SCHEMA_NAME,
  FOREIGN_TABLE.TABLE_NAME,
  FOREIGN_TABLE.COLUMN_NAME,
  CONSTR.CONSTRAINT_NAME,
  BASE_TABLE.OWNER AS BASE_TABLE_SCHEMA_NAME,
  BASE_TABLE.TABLE_NAME AS BASE_TABLE_NAME,
  BASE_TABLE.COLUMN_NAME AS BASE_TABLE_COLUMN,
  FOREIGN_TABLE.TABLE_NAME || '.' || FOREIGN_TABLE.COLUMN_NAME || ' = ' || BASE_TABLE.TABLE_NAME || '.' || BASE_TABLE.COLUMN_NAME AS JOIN_CONDITION
FROM ALL_CONSTRAINTS CONSTR
JOIN ALL_CONS_COLUMNS FOREIGN_TABLE ON (FOREIGN_TABLE.OWNER = CONSTR.OWNER AND FOREIGN_TABLE.CONSTRAINT_NAME = CONSTR.CONSTRAINT_NAME)
JOIN ALL_CONS_COLUMNS BASE_TABLE ON (BASE_TABLE.CONSTRAINT_NAME = CONSTR.R_CONSTRAINT_NAME AND BASE_TABLE.OWNER = CONSTR.R_OWNER AND BASE_TABLE.POSITION = FOREIGN_TABLE.POSITION)
WHERE CONSTR.R_OWNER NOT IN ('SYS',
                             'SYSTEM',
                             'OLAPSYS',
                             'DBSNMP',
                             'EXFSYS',
                             'MDSYS',
                             'CTXSYS',
                             'SYSMAN')
AND LOWER(FOREIGN_TABLE.TABLE_NAME) LIKE '%'
ORDER BY
  FOREIGN_TABLE.TABLE_NAME,
  FOREIGN_TABLE.COLUMN_NAME
;

この SQL を実行すると次のような結果が得られます。参考例として結果表示のために SYS スキーマのデータを表示しています。

それぞれの列の意味は次の通りです。

TABLE_SCHEMA_NAME      : 参照元のテーブルのスキーマ名
TABLE_NAME             : 参照元のテーブル名
COLUMN_NAME            : 参照元のテーブルの列名
CONSTRAINT_NAME        : 制約名
BASE_TABLE_SCHEMA_NAME : 参照されるテーブルのスキーマ名
BASE_TABLE_NAME        : 参照されるテーブル名
BASE_TABLE_COLUMN      : 参照されるテーブルの列名
JOIN_CONDITION         : テーブル結合条件

この SQL を使うことで各テーブルの外部キー一覧を一度に確認することが出来、各テーブルの関連を簡単に確認することが出来ます。