게시일: Feb 05, 2013 11:3:26 PM
다음은 내가 만들어서 사용하고 있는 테이블 정의서 스크립트이다.
SELECT A.TABLE_NAME TID
, B.COMMENTS TNM
, A.COLUMN_NAME CID
, C.COMMENTS CNM
, DECODE(A.DATA_TYPE, 'VARCHAR2' , 'VC2'
, 'NUMBER' , 'N'
, 'DATE' , 'D'
, A.DATA_TYPE) DTYPE
-- , A.DATA_LENGTH
, DECODE(A.DATA_TYPE, 'NUMBER' , A.DATA_PRECISION||'.'||A.DATA_SCALE
, 'FLOAT' , A.DATA_PRECISION||'.'||A.DATA_SCALE
, A.DATA_LENGTH) DATA_LENGTH
, DECODE(A.NULLABLE, 'Y', NULL, 'N') NULLYN
, A.DATA_DEFAULT D_DEFAULT
, DECODE(D.CONSTRAINT_NAME, NULL, NULL
, D.CONSTRAINT_NAME||'('||D.POSITION||')') CONS_NM1
-- , DECODE(D.CONSTRAINT_NAME, NULL, DECODE(E.INDEX_NAME, NULL, NULL
-- , E.INDEX_NAME|| '('||E.COLUMN_POSITION||')')
-- , D.CONSTRAINT_NAME||'('||D.POSITION||')') CONS_NM1
FROM
-- USER_IND_COLUMNS E,
USER_CONS_COLUMNS D
, USER_COL_COMMENTS C
, USER_TAB_COMMENTS B
, USER_TAB_COLUMNS A
WHERE A.TABLE_NAME = upper('&table_name')
and A.COLUMN_NAME = nvl(upper('&column_name'), A.COLUMN_NAME)
AND B.TABLE_NAME = A.TABLE_NAME
AND C.TABLE_NAME = A.TABLE_NAME
AND C.COLUMN_NAME = A.COLUMN_NAME
AND D.TABLE_NAME(+) = A.TABLE_NAME
AND D.COLUMN_NAME(+) = A.COLUMN_NAME
AND D.CONSTRAINT_NAME(+) = A.TABLE_NAME||'_PK'
-- AND E.TABLE_NAME(+) = A.TABLE_NAME
-- AND E.COLUMN_NAME(+) = A.COLUMN_NAME
ORDER BY D.POSITION, A.COLUMN_ID
;