[Oracle] 오라클 테이블 명세서 쿼리

select TBL.TABLE_NAME , TCM.COMMENTS , TBL.TABLESPACE_NAME , TCL.COLUMN_ID , TCL.COLUMN_NAME , 

    case when TCL.DATA_TYPE = 'VARCHAR2' or TCL.DATA_TYPE = 'CHAR' or TCL.DATA_TYPE = 'NUMBER' 

      then TCL.DATA_TYPE || '(' || DATA_LENGTH || ')'

   else TCL.DATA_TYPE end as DATA_TYPE , CON.KEY , 

    decode(NULLABLE , 'N' , 'NOT NULL' , '') AS NOTNULL , DATA_DEFAULT , CCM.COMMENTS     

from USER_TABLES TBL , USER_TAB_COMMENTS TCM , USER_TAB_COLUMNS TCL , USER_COL_COMMENTS CCM , 

  (select CCL.TABLE_NAME , COLUMN_NAME ,

      case when sum(decode(CONSTRAINT_TYPE , 'P' , 1 , 0)) > 0 and  sum(decode(CONSTRAINT_TYPE , 'F' , 1 , 0)) > 0 

        then 'PK,FK'

     when sum(decode(CONSTRAINT_TYPE , 'P' , 1 , 0)) > 0

     then 'PK'

     when sum(decode(CONSTRAINT_TYPE , 'F' , 1 , 0)) > 0

     then 'FK'

     else '' end as KEY ,

      sum(decode(CONSTRAINT_TYPE , 'C' , 0 , 'P' , 0 , 'F' , 0 , 1)) as CCC              

  from USER_CONS_COLUMNS CCL , USER_CONSTRAINTS CNS

  where CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME

  group by CCL.TABLE_NAME , COLUMN_NAME ) CON

where TBL.TABLE_NAME = TCM.TABLE_NAME

   and TBL.TABLE_NAME = TCL.TABLE_NAME 

   and TCL.TABLE_NAME = CCM.TABLE_NAME and TCL.COLUMN_NAME = CCM.COLUMN_NAME

   and TCL.TABLE_NAME = CON.TABLE_NAME(+) and TCL.COLUMN_NAME = CON.COLUMN_NAME(+)

order by TBL.TABLE_NAME , COLUMN_ID;