[oracle] 컬럼정보 조회

SELECT

 A.TABLE_NAME, NVL(A.COMMENTS, '') AS T_CON,

 B.COLUMN_NAME,  B.DATA_PRECISION, B.DATA_SCALE,

 DECODE(B.NULLABLE, 'Y', 'NULL', 'N', 'NOT NULL') AS NOT_NULL,

 NVL(C.COMMENTS, '') AS COL_CON,

 B.DATA_TYPE, B.DATA_LENGTH, B.COLUMN_ID

 , (

     SELECT DECODE(UC.CONSTRAINT_TYPE, 'P',POSITION , '0') PRIMARY_KEY

     FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC

     WHERE UCC.TABLE_NAME = UC.TABLE_NAME

     AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME

     AND UC.CONSTRAINT_TYPE = 'P'

     AND UCC.TABLE_NAME = A.TABLE_NAME

     AND UCC.COLUMN_NAME = B.COLUMN_NAME

 ) PRIMARY_KEY

 FROM

 USER_TAB_COMMENTS A, USER_TAB_COLUMNS B, USER_COL_COMMENTS C

 WHERE

 A.TABLE_NAME = B.TABLE_NAME

 AND A.TABLE_NAME = C.TABLE_NAME

 AND B.COLUMN_NAME = C.COLUMN_NAME

 AND A.TABLE_NAME = 'EMP'

 ORDER BY  A.TABLE_NAME, B.COLUMN_ID