간단하게 테이블 정의서(table layout)를 쿼리로 출력한다.
TABLE_COMMENTS (테이블 코멘트)
TABLE_NAME (테이블명)
COLUMN_COMMENTS (컬럼 코멘트)
COLUMN_NAME (컬럼명)
PK_FLAG (pk 여부)
FK_FLAG (fk 여부)
NULL_FLAG (null 여부)
DATA_TYPE (테이터타입)
DATA_LENGTH (데이터 길이)
SELECT A1.TABLE_COMMENTS TABLE_COMMENTS
, A1.TABLE_NAME TABLE_NAME
, A1.COLUMN_COMMENTS COLUMN_COMMENTS
, A1.COLUMN_NAME COLUMN_NAME
, (CASE
WHEN B1.CONSTRAINT_TYPE = 'P'
THEN 'Y'
END) PK_FLAG
, (CASE
WHEN B1.CONSTRAINT_TYPE = 'R'
THEN 'Y'
END) FK_FLAG
, A1.NULL_FLAG
, A1.DATA_TYPE
, A1.DATA_LENGTH
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
, (CASE
WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
THEN '(' || A.DATA_LENGTH || ')'
WHEN A.DATA_TYPE = 'NUMBER'
AND A.DATA_SCALE = 0
AND A.DATA_PRECISION IS NOT NULL
THEN '(' || A.DATA_PRECISION || ')'
WHEN A.DATA_TYPE = 'NUMBER'
AND A.DATA_SCALE <> 0
THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE
|| ')'
END
) DATA_LENGTH
, A.COLUMN_ID
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
)) 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(+))
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID
'
-- ansi sql을 사용해서 아래와 같이 바꾸어 보았다.
SELECT A1.TABLE_NAME TABLE_NAME
, A1.TABLE_COMMENTS TABLE_COMMENTS
, A1.COLUMN_NAME COLUMN_NAME
, A1.COLUMN_COMMENTS COLUMN_COMMENTS
, DECODE (B1.CONSTRAINT_TYPE, 'P', 'Y') PK_FLAG
, DECODE (B1.CONSTRAINT_TYPE, 'R', 'Y') FK_FLAG
, A1.NULL_FLAG
, A1.DATA_TYPE
, A1.DATA_LENGTH
FROM (SELECT B.COMMENTS TABLE_COMMENTS
, A.TABLE_NAME TABLE_NAME
, C.COMMENTS COLUMN_COMMENTS
, A.COLUMN_NAME COLUMN_NAME
, DECODE (A.NULLABLE, 'Y', 'Y') NULL_FLAG
, A.DATA_TYPE DATA_TYPE
, (CASE
WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
THEN '(' || A.DATA_LENGTH || ')'
WHEN A.DATA_TYPE = 'NUMBER'
AND A.DATA_SCALE = 0
AND A.DATA_PRECISION IS NOT NULL
THEN '(' || A.DATA_PRECISION || ')'
WHEN A.DATA_TYPE = 'NUMBER'
AND A.DATA_SCALE <> 0
THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
END
) DATA_LENGTH
, A.COLUMN_ID
FROM USER_TAB_COLUMNS A JOIN USER_TAB_COMMENTS B ON (A.TABLE_NAME = B.TABLE_NAME)
JOIN USER_COL_COMMENTS C ON ( A.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME)
) A1
LEFT JOIN
(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
ON ( A1.TABLE_NAME = B1.TABLE_NAME
AND A1.COLUMN_NAME = B1.COLUMN_NAME)
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID
