【Oracle】ユーザーテーブルの情報抽出

必要なオブジェクトだけ欲しい場合は、コメントのところで条件を指定する

SELECT
	T.TABLE_NAME	AS "テーブル物理名",
	TC.COMMENTS	AS "テーブル論理名",
    T.tablespace_name AS "テーブルスペース名",
	C.COLUMN_ID	AS "カラムID",
	CASE WHEN PK.COLUMN_POSITION IS NOT NULL THEN PK.COLUMN_POSITION ELSE NULL END AS "PK",
	C.COLUMN_NAME	AS "カラム物理名",
	CC.COMMENTS	AS "カラム論理名",
	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 "デフォルト値"
FROM
	USER_TABLES T
	INNER JOIN USER_TAB_COMMENTS TC ON (
		T.TABLE_NAME = TC.TABLE_NAME
	)
	INNER JOIN USER_TAB_COLUMNS C ON (
		T.TABLE_NAME = C.TABLE_NAME
	)
	INNER JOIN USER_COL_COMMENTS CC ON (
		T.TABLE_NAME = CC.TABLE_NAME AND
		C.COLUMN_NAME = CC.COLUMN_NAME
	)
	LEFT JOIN (
		SELECT
			IND.INDEX_NAME,
			CONST.TABLE_NAME,
			IND.COLUMN_NAME,
			IND.COLUMN_POSITION
		FROM
			USER_IND_COLUMNS IND
			INNER JOIN USER_CONSTRAINTS CONST ON (
				IND.INDEX_NAME = CONST.CONSTRAINT_NAME AND
				CONSTRAINT_TYPE = 'P'
			)
	) PK ON (
		T.TABLE_NAME = PK.TABLE_NAME AND
		C.COLUMN_NAME = PK.COLUMN_NAME
	)

/*WHERE T.TABLE_NAME='AAAA'*/

ORDER BY
	T.TABLE_NAME,
	C.COLUMN_ID