테이블 정의서 스크립트

게시일: 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

;