[오라클] DB 테이블 명세서 출력 쿼리(Oracle Table Report ) > db

Select TBL.TABLE_NAME, TCM.COMMENTS
 , TBL.TABLESPACE_NAME
 , TCL.COLUMN_ID "No"
 , TCL.COLUMN_NAME  "컬럼ID"
 , Case When TCL.DATA_TYPE = 'VARCHAR2' Or TCL.DATA_TYPE = 'CHAR'
   Then TCL.DATA_TYPE || '(' || DATA_length || ')'
  When TCL.DATA_TYPE = 'NUMBER' And data_precision > 0 And data_scale > 0
   Then TCL.DATA_TYPE || '(' || data_precision || ',' || data_scale || ')'
  When TCL.DATA_TYPE = 'NUMBER' And data_precision > 0
   Then TCL.DATA_TYPE || '(' || data_precision || ')'
  When TCL.DATA_TYPE = 'NUMBER'
   Then TCL.DATA_TYPE || '()'
  Else TCL.DATA_TYPE
 End As  "데이타타입"
 , CON.KEY, Decode(Nullable , 'N' , 'No' , 'Yes') As "Null"
 , DATA_DEFAULT "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 , 'R' , 1 , 0)) > 0
     Then 'PK,FK'
    When Sum(Decode(CONSTRAINT_TYPE , 'P' , 1 , 0)) > 0
     Then 'PK'
    When Sum(decode(CONSTRAINT_TYPE , 'R' , 1 , 0)) > 0
     Then 'FK'
    Else ''
   End As KEY
   , Sum(Decode(CONSTRAINT_TYPE , 'C' , 0 , 'P' , 0 , 'R' , 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