Oracle データベースでテーブルの詳細情報(各列の詳細情報と主キー・外部キー等)を一覧表示する
Oracle データベースでテーブルの各列の詳細情報(各列の定義や主キー・外部キー・NULL可否等)を一覧表示する場合、次の SQL で一度に取得することが出来ます。SELECT COL.OWNER AS "スキーマ名",
COL.TABLE_NAME AS "テーブル名",
COL.COLUMN_ID AS "列の順序番号",
COL.COLUMN_NAME AS "列名",
COL.DATA_TYPE AS "データ型",
DECODE(CHAR_LENGTH, 0, DATA_TYPE, DATA_TYPE || '(' || CHAR_LENGTH || ')') AS "拡張データ型",
COL.DATA_LENGTH AS "列の長さ(バイト)",
COL.DATA_PRECISION AS "少数桁数",
COL.DATA_SCALE AS "数値の小数点以下の桁",
COL.NULLABLE AS "NULL可否",
COL.DATA_DEFAULT AS "デフォルト値の長さ",
NVL(PK.PRIMARY_KEY, ' ') AS "主キー",
NVL(FK.FOREIGN_KEY, ' ') AS "外部キー",
NVL(UK.UNIQUE_KEY, ' ') AS "ユニークキー",
NVL(CHECK_CONST.CHECK_CONSTRAINT, ' ') AS "チェック制約",
COMM.COMMENTS AS "コメント"
FROM ALL_TABLES TAB
JOIN ALL_TAB_COLUMNS COL ON (COL.OWNER = TAB.OWNER
AND COL.TABLE_NAME = TAB.TABLE_NAME)
LEFT JOIN ALL_COL_COMMENTS COMM ON (COL.OWNER = COMM.OWNER
AND COL.TABLE_NAME = COMM.TABLE_NAME
AND COL.COLUMN_NAME = COMM.COLUMN_NAME)
LEFT JOIN
(SELECT CONSTR.OWNER,
COL_CONST.TABLE_NAME,
COL_CONST.COLUMN_NAME,
'PK' PRIMARY_KEY
FROM ALL_CONSTRAINTS CONSTR
JOIN ALL_CONS_COLUMNS COL_CONST ON CONSTR.CONSTRAINT_NAME = COL_CONST.CONSTRAINT_NAME
AND COL_CONST.OWNER = CONSTR.OWNER
WHERE CONSTR.CONSTRAINT_TYPE = 'P') PK ON (COL.TABLE_NAME = PK.TABLE_NAME
AND COL.COLUMN_NAME = PK.COLUMN_NAME
AND COL.OWNER = PK.OWNER)
LEFT JOIN
(SELECT CONSTR.OWNER,
COL_CONST.TABLE_NAME,
COL_CONST.COLUMN_NAME,
'FK' FOREIGN_KEY
FROM ALL_CONSTRAINTS CONSTR
JOIN ALL_CONS_COLUMNS COL_CONST ON CONSTR.CONSTRAINT_NAME = COL_CONST.CONSTRAINT_NAME
AND COL_CONST.OWNER = CONSTR.OWNER
WHERE CONSTR.CONSTRAINT_TYPE = 'R'
GROUP BY CONSTR.OWNER,
COL_CONST.TABLE_NAME,
COL_CONST.COLUMN_NAME) FK ON (COL.TABLE_NAME = FK.TABLE_NAME
AND COL.COLUMN_NAME = FK.COLUMN_NAME
AND COL.OWNER = FK.OWNER)
LEFT JOIN
(SELECT CONSTR.OWNER,
COL_CONST.TABLE_NAME,
COL_CONST.COLUMN_NAME,
'UK' UNIQUE_KEY
FROM ALL_CONSTRAINTS CONSTR
JOIN ALL_CONS_COLUMNS COL_CONST ON CONSTR.CONSTRAINT_NAME = COL_CONST.CONSTRAINT_NAME
AND CONSTR.OWNER = COL_CONST.OWNER
WHERE CONSTR.CONSTRAINT_TYPE = 'U'
UNION SELECT IND.OWNER,
COL_IND.TABLE_NAME,
COL_IND.COLUMN_NAME,
'UK' UNIQUE_KEY
FROM ALL_INDEXES IND
JOIN ALL_IND_COLUMNS COL_IND ON IND.INDEX_NAME = COL_IND.INDEX_NAME
WHERE IND.UNIQUENESS = 'UNIQUE') UK ON (COL.TABLE_NAME = UK.TABLE_NAME
AND COL.COLUMN_NAME = UK.COLUMN_NAME
AND COL.OWNER = UK.OWNER)
LEFT JOIN
(SELECT CONSTR.OWNER,
COL_CONST.TABLE_NAME,
COL_CONST.COLUMN_NAME,
'Check' CHECK_CONSTRAINT
FROM ALL_CONSTRAINTS CONSTR
JOIN ALL_CONS_COLUMNS COL_CONST ON CONSTR.CONSTRAINT_NAME = COL_CONST.CONSTRAINT_NAME
AND COL_CONST.OWNER = CONSTR.OWNER
WHERE CONSTR.CONSTRAINT_TYPE = 'C'
GROUP BY CONSTR.OWNER,
COL_CONST.TABLE_NAME,
COL_CONST.COLUMN_NAME) CHECK_CONST ON (COL.TABLE_NAME = CHECK_CONST.TABLE_NAME
AND COL.COLUMN_NAME = CHECK_CONST.COLUMN_NAME
AND COL.OWNER = CHECK_CONST.OWNER)
WHERE COL.OWNER NOT IN ('SYS',
'SYSTEM',
'OLAPSYS',
'DBSNMP',
'EXFSYS',
'MDSYS',
'CTXSYS',
'SYSMAN')
ORDER BY COL.OWNER,
COL.TABLE_NAME,
COL.COLUMN_ID,
COL.COLUMN_NAME
;
この SQL を実行することで次のような結果を得ることが出来ます。ここでは SCOTT ユーザーのテーブルの一覧を表示しています。
また列の詳細な説明は下記 Oracle のドキュメントに記載されています。
https://docs.oracle.com/cd/E60665_01/db112/REFRN/statviews_2103.htm
