【Oracle】テーブル構造を取得するSQL

Tweet

Category develop 2014/09/10

問題

Oracleのテーブル構造を出力せよ。

答え

Oracleのテーブル構造を出力するSQLです（PK、型と長さ、NULLABLE、初期値、カラムコメント）

さくっと知りたいけどツールもすぐに用意できない、資料もすぐに見つからない時などに。

SELECT
	C.COLUMN_NAME AS "COLUMN",
	CASE WHEN PK.COLUMN_POSITION IS NOT NULL THEN PK.COLUMN_POSITION ELSE NULL END AS "PK",
	C.DATA_TYPE AS "型",
	CASE WHEN C.DATA_PRECISION IS NOT NULL
		THEN C.DATA_PRECISION || ',' || C.DATA_SCALE
		ELSE TO_CHAR(C.DATA_LENGTH)
	END AS "長さ",
	C.NULLABLE AS "NULL",
	C.DATA_DEFAULT AS "初期値",
	CC.COMMENTS AS "コメント"
FROM
	ALL_TABLES T
	INNER JOIN ALL_TAB_COLUMNS C ON T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME
	INNER JOIN ALL_COL_COMMENTS CC ON T.OWNER = CC.OWNER AND T.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME
	LEFT JOIN (
		SELECT
			IDX.TABLE_OWNER,
			IDX.INDEX_NAME,
			CONST.TABLE_NAME,
			IDX.COLUMN_NAME,
			IDX.COLUMN_POSITION
		FROM
			ALL_IND_COLUMNS IDX
			INNER JOIN ALL_CONSTRAINTS CONST ON IDX.INDEX_NAME = CONST.CONSTRAINT_NAME AND CONST.CONSTRAINT_TYPE = 'P'
	) PK ON T.OWNER = PK.TABLE_OWNER AND T.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME
WHERE
	T.OWNER = 'ここにスキーマ' AND T.TABLE_NAME LIKE 'ここにテーブル'
ORDER BY
	T.TABLE_NAME,
	C.COLUMN_ID

-- テーブル自体のコメントは下記に
-- 	INNER JOIN ALL_TAB_COMMENTS TC ON T.OWNER = TC.OWNER AND T.TABLE_NAME = TC.TABLE_NAME