Oracleのテーブル定義を取得するSQLメモ。
#テーブル一覧
SELECT T.TABLE_NAME
, C.COMMENTS
, O.CREATED
, O.LAST_DDL_TIME
FROM ALL_TABLES T
LEFT JOIN ALL_TAB_COMMENTS C
ON T.OWNER=C.OWNER AND T.TABLE_NAME=C.TABLE_NAME
LEFT JOIN ALL_OBJECTS O
ON T.OWNER=O.OWNER AND T.TABLE_NAME=O.OBJECT_NAME
WHERE T.OWNER=user
ORDER BY T.TABLE_NAME
#テーブル定義1
SELECT T.COLUMN_ID
, P.CONSTRAINT_TYPE PK
, T.COLUMN_NAME
, T.DATA_TYPE
, CASE
WHEN T.DATA_TYPE IN ('VARCHAR2','NVARCHAR2','CHAR','NCHAR') THEN TO_CHAR(T.CHAR_LENGTH) || DECODE(T.CHAR_USED,'C',' CHAR',' BYTE')
WHEN T.DATA_TYPE='NUMBER' THEN TO_CHAR(T.DATA_PRECISION) || ',' ||TO_CHAR(T.DATA_SCALE)
WHEN T.DATA_TYPE='RAW' THEN TO_CHAR(T.DATA_LENGTH)
ELSE NULL
END DATALENGTH
, T.DATA_DEFAULT
, T.NULLABLE
, M.COMMENTS
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_COL_COMMENTS M
ON T.OWNER=M.OWNER
AND T.TABLE_NAME=M.TABLE_NAME
AND T.COLUMN_NAME=M.COLUMN_NAME
LEFT JOIN (SELECT S.OWNER
, S.TABLE_NAME
, COL.COLUMN_NAME
, S.CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS S
INNER JOIN ALL_CONS_COLUMNS COL
ON S.OWNER=COL.OWNER
AND S.TABLE_NAME=COL.TABLE_NAME
AND S.CONSTRAINT_NAME=COL.CONSTRAINT_NAME
AND S.CONSTRAINT_TYPE='P') P
ON T.OWNER=P.OWNER
AND T.TABLE_NAME=P.TABLE_NAME
AND T.COLUMN_NAME=P.COLUMN_NAME
WHERE T.OWNER= USER --current userの情報
AND T.TABLE_NAME=:table_name
ORDER BY T.COLUMN_ID
結果例：
#テーブル定義2 (UNIQUE制約 4つまでの情報取得)
WITH UK_lIST AS (
SELECT *
FROM (
SELECT S.OWNER
, S.TABLE_NAME
, COL.COLUMN_NAME
, S.CONSTRAINT_TYPE
, S.CONSTRAINT_NAME
, DENSE_RANK() OVER(ORDER BY S.CONSTRAINT_NAME) R
FROM ALL_CONSTRAINTS S
INNER JOIN ALL_CONS_COLUMNS COL
ON S.OWNER = USER
AND S.TABLE_NAME = :table_name
AND S.OWNER=COL.OWNER
AND S.TABLE_NAME=COL.TABLE_NAME
AND S.CONSTRAINT_NAME=COL.CONSTRAINT_NAME
AND S.CONSTRAINT_TYPE='U'
)
PIVOT (MAX(CONSTRAINT_NAME) FOR R IN (1 as UK1, 2 AS UK2,3 AS UK3,4 AS UK4))
)
SELECT T.COLUMN_ID
, P.CONSTRAINT_TYPE PK
, T.COLUMN_NAME
, T.DATA_TYPE
, CASE
WHEN T.DATA_TYPE IN ('VARCHAR2','NVARCHAR2','CHAR','NCHAR') THEN TO_CHAR(T.CHAR_LENGTH) || DECODE(T.CHAR_USED,'C',' CHAR',' BYTE')
WHEN T.DATA_TYPE='NUMBER' THEN TO_CHAR(T.DATA_PRECISION) || ',' ||TO_CHAR(T.DATA_SCALE)
WHEN T.DATA_TYPE='RAW' THEN TO_CHAR(T.DATA_LENGTH)
ELSE NULL
END DATALENGTH
, T.DATA_DEFAULT
, T.NULLABLE
, M.COMMENTS
, U.UK1
, U.UK2
, U.UK3
, U.UK4
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_COL_COMMENTS M
ON T.OWNER=M.OWNER
AND T.TABLE_NAME=M.TABLE_NAME
AND T.COLUMN_NAME=M.COLUMN_NAME
LEFT JOIN (SELECT S.OWNER
, S.TABLE_NAME
, COL.COLUMN_NAME
, S.CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS S
INNER JOIN ALL_CONS_COLUMNS COL
ON S.OWNER=COL.OWNER
AND S.TABLE_NAME=COL.TABLE_NAME
AND S.CONSTRAINT_NAME=COL.CONSTRAINT_NAME
AND S.CONSTRAINT_TYPE='P') P
ON T.OWNER=P.OWNER
AND T.TABLE_NAME=P.TABLE_NAME
AND T.COLUMN_NAME=P.COLUMN_NAME
LEFT JOIN UK_lIST U
ON T.OWNER=U.OWNER
AND T.TABLE_NAME=U.TABLE_NAME
AND T.COLUMN_NAME=U.COLUMN_NAME
WHERE T.OWNER= USER --current user
AND T.TABLE_NAME=:table_name
ORDER BY T.COLUMN_ID
結果例：
