[8] DB 테이블 정의서 쿼리
SELECT TT.* ,
C1.DATA_DEFAULT
FROM (SELECT A1.TABLE_COMMENTS ,
A1.TABLE_NAME ,
A1.COLUMN_ID ,
A1.COLUMN_NAME ,
A1.COLUMN_COMMENTS ,
(
CASE
WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'Y'
END) PK_FLAG ,
NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG ,
A1.DATA_TYPE|| (
CASE A1.DATA_TYPE
WHEN 'NUMBER' THEN '('||TO_CHAR(A1.DATA_LENGTH)||','||TO_CHAR(A1.DATA_PRECISION)||')'
WHEN 'DATE' THEN ' '
ELSE '('||A1.DATA_LENGTH||')'
END) DATA_TYPE
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 'AC_BANK%'
) TT ,
COLS C1 WHERE TT.TABLE_NAME = C1.TABLE_NAME
AND TT.COLUMN_NAME = C1.COLUMN_NAME
ORDER BY TT.TABLE_NAME, TT.COLUMN_ID;
