[ORACLE] 오라클 테이블정의서용 쿼리(테이블단위) / 테이블명세서 / 테이블정의서

SELECT A1.TABLE_COMMENTS
, A1.TABLE_NAME
-- , A1.COLUMN_ID
, A1.COLUMN_COMMENTS AS COLUMN_NAME
, A1.COLUMN_NAME AS COLUMN_ID
, A1.DATA_TYPE AS DATA_TYPE
, (CASE WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'PK' END) PK_FLAG
, (CASE A1.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(A1.DATA_LENGTH)
WHEN 'DATE' THEN ' '
ELSE TO_CHAR(A1.DATA_LENGTH) END) AS DATA_LENGTH
, NVL(A1.NULL_FLAG, 'NOT NULL') AS NULL_FLAG

FROM (SELECT B.COMMENTS TABLE_COMMENTS
, A.TABLE_NAME TABLE_NAME
, C.COMMENTS COLUMN_COMMENTS
, A.COLUMN_NAME COLUMN_NAME
, (CASE A.NULLABLE WHEN 'Y' THEN 'Y' END) NULL_FLAG
, A.DATA_TYPE DATA_TYPE
, A.DATA_LENGTH
, A.COLUMN_ID AS COLUMN_ID
, A.DATA_PRECISION
FROM USER_TAB_COLUMNS A
, USER_TAB_COMMENTS B
, USER_COL_COMMENTS C
WHERE (A.TABLE_NAME = B.TABLE_NAME)
AND ( A.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME
)
AND B.TABLE_TYPE = 'TABLE') A1
, (SELECT A.TABLE_NAME
, A.COLUMN_NAME
, B.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS A
, USER_CONSTRAINTS B
WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE ( A1.TABLE_NAME = B1.TABLE_NAME(+)
AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
AND A1.TABLE_NAME LIKE '테이블명%'
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID;