테이블의 컬럼/코멘트/컬럼사이즈/PK 뽑아내기

WITH ALL_TAB_PRIMARY_KEY AS (
SELECT — 테이블의 PK컬럼
CONS.OWNER, COLS.TABLE_NAME, COLS.COLUMN_NAME, COLS.POSITION, CONS.STATUS, ‘P’ AS PRIMARY_KEY
FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
WHERE CONS.CONSTRAINT_TYPE = ‘P’
AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
–AND CONS.TABLE_NAME = ‘(YOUR_TALBE_NAME)’
)
SELECT A.OWNER,A.TABLE_NAME,B.COMMENTS AS TABLE_DESC
,A.COLUMN_NAME,C.COMMENTS AS COLUMN_DESC,D.PRIMARY_KEY
,A.COLUMN_ID,A.DATA_TYPE,A.DATA_LENGTH,A.DATA_SCALE,A.NULLABLE
FROM ALL_TAB_COLUMNS A
,ALL_TAB_COMMENTS B
,ALL_COL_COMMENTS C
,ALL_TAB_PRIMARY_KEY D
WHERE A.OWNER = ‘(OWNER_NAME)’
AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME
AND A.OWNER = C.OWNER(+) AND A.TABLE_NAME = C.TABLE_NAME(+) AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER = D.OWNER(+) AND A.TABLE_NAME = D.TABLE_NAME(+) AND A.COLUMN_NAME = D.COLUMN_NAME(+)
AND A.TABLE_NAME IN ( ‘(YOUR_TALBE_NAME)’ )

Leave a Reply

Your email address will not be published.