[ORACLE] View Oracle Table and Column information

--COLUMN INFORMATION FOR TABLE

SELECT

  TABLE_NAME

  , COLUMN_NAME

  , DATA_TYPE

  , DATA_LENGTH

  , DATA_PRESICION

  , DATA_SCALE

  , NULLABLE

  , COLUMN_ID

  , DATA_DEFAULT

  , CHAR_LENGTH

FROM

  USER_TAB_COLUMNS

WHERE

  TABLE_NAME = 'TABLE_NAME'

--FIELD COMENT

SELECT

  COLUMN_NAME

  , COMMENTS

FROM

  USER_COL_COMMENTS

WHERE

  TABLE_NAME='TABLE_NAME'

  

--COLUMN INFORMATION FOR TABLE

SELECT

  A.COLUMN_ID

  , B.COMMENTS

  , A.COLUMN_NAME

  , A.DATA_TYPE || '(' ||

    DECODE(

      A.DATA_TYPE

      , 'NUMBER'

      , A.DATA_PRECISION ||

        DECODE(A.DATA_SCALE, 0,'', ',' || A.DATA_SCALE)

      , A.DATA_LENGTH) || ')' AS DATA_TYPE --TYPE

  , A.DATA_SCALE

  , A.NULLABLE                --NULLABLE

  , A.DATA_DEFAULT            --DEFUALT VALUE

FROM

  USER_TAB_COLUMNS A, USER_COL_COMMENTS B

WHERE

  A.TABLE_NAME = B.TABLE_NAME

  AND A.COLUMN_NAME = B.COLUMN_NAME

  AND A.TABLE_NAME = 'TABLE_NAME'

ORDER BY

  A.COLUMN_ID

출처 : http://welchsy.tistory.com/267