테이블 명세서 정리

SELECT 
A.COMMENTS   TABLE_DESC         -- 테이블명
, A.TABLE_NAME   TABLE_ID       -- 테이블 ID
, C.COMMENTS    COLUMN_DESC     --칼럼명
, B.COLUMN_NAME COLUMN_NAME     -- 컬럼 ID
, B.DATA_TYPE --|| '(' || --타입
, DECODE(B.DATA_PRECISION, NULL, TO_CHAR(B.DATA_LENGTH) , TO_CHAR(B.DATA_PRECISION) || ',' || TO_CHAR(B.DATA_SCALE))   LENGTH -- 데이터 길이
, DECODE(B.NULLABLE, 'N', 'Y', '') NOT_NULL
, DECODE(P.COLUMN_NAME, NULL, '', 'Y') PK_YN --PK
, DECODE(R.COLUMN_NAME, NULL, '', 'Y') FK_YN --FK
, DECODE(U.COLUMN_NAME, NULL, '', 'Y') INDEX_YN --INDEX
, B.COLUMN_ID   COLUMN_ORDER    -- 컬럼순서
FROM ALL_TAB_COMMENTS A
, ALL_TAB_COLUMNS B
, ALL_COL_COMMENTS C
, (SELECT W.OWNER, W.CONSTRAINT_NAME, W.TABLE_NAME          , V.COLUMN_NAME
   FROM ALL_CONS_COLUMNS V, ALL_CONSTRAINTS W
   WHERE W.CONSTRAINT_TYPE = 'P'
   AND W.CONSTRAINT_NAME = V.CONSTRAINT_NAME
   AND W.OWNER = V.OWNER ) P
, (SELECT W.OWNER, W.CONSTRAINT_NAME, W.TABLE_NAME
          , V.COLUMN_NAME
   FROM ALL_CONS_COLUMNS V, ALL_CONSTRAINTS W
   WHERE W.CONSTRAINT_TYPE = 'R'
   AND W.CONSTRAINT_NAME = V.CONSTRAINT_NAME
   AND W.OWNER = V.OWNER ) R
, (SELECT W.OWNER, W.CONSTRAINT_NAME, W.TABLE_NAME
          , V.COLUMN_NAME
   FROM ALL_CONS_COLUMNS V, ALL_CONSTRAINTS W
   WHERE W.CONSTRAINT_TYPE = 'U'
   AND W.CONSTRAINT_NAME = V.CONSTRAINT_NAME
   AND W.OWNER = V.OWNER ) U
WHERE A.OWNER = 'NITS2'
AND B.OWNER = A.OWNER
AND B.TABLE_NAME = A.TABLE_NAME
AND C.OWNER = B.OWNER
AND C.TABLE_NAME = B.TABLE_NAME
AND C.COLUMN_NAME = B.COLUMN_NAME
AND P.OWNER(+) = B.OWNER
AND P.TABLE_NAME(+) = B.TABLE_NAME
AND P.COLUMN_NAME(+) = B.COLUMN_NAME
AND R.OWNER(+) = B.OWNER
AND R.TABLE_NAME(+) = B.TABLE_NAME
AND R.COLUMN_NAME(+) = B.COLUMN_NAME
AND U.OWNER(+) = B.OWNER
AND U.TABLE_NAME(+) = B.TABLE_NAME
AND U.COLUMN_NAME(+) = B.COLUMN_NAME
AND A.TABLE_NAME='REQDTL'
ORDER BY A.OWNER, A.TABLE_NAME, B.COLUMN_ID

SI-20090902_QFPK2250-(첨부)테이블정의서_정산 v1.2.xls

테이블명세작성script.txt