테이블 내역서 만들기

------------------ 기존 쿼리 ---------------------------------------------------------

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

-------------------------------------------------------------------------->

-------------------------------- 테이블 리스크 쿼리 -------------------------

 SELECT a.table_name "테이블명", b.COMMENTS "테이블설명", a.initial_extent "초기", nvl(next_extent, '') "증가", '' "비고"
 FROM  user_tables a,
    user_tab_comments b
 WHERE 1 = 1
    and a.table_name = b.table_name
 order by a.table_name
   
--------------------------------------------------------------------------->

-------------------------------- 변경한 테이블 명세서 쿼리 ------------------
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'
          then TCL.DATA_TYPE || '(' || data_precision || ',' || data_scale || ')'
      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

------------------------------------------------------------------------------->

-------------------------- 테이블 명세서 쿼리 참조 -----------------------------

-- 테이블 명세서 추출 쿼리
select
        dtc.owner  소유자 ,
        dt.table_type 객체형태,
        dt.comments 객체설명 ,
        dtc.table_name 테이블명,
        dtc.column_name 컬럼명,
        dcc.comments 컬럼설명,
        decode(data_length, null, dtc.data_type ,  decode(dtc.data_type,'DATE', data_type,
                                                                        'LONG', data_type,
                                                                        'LONG ROW', data_type,
                                                                        'CLOB', data_type,
                                                                        'BLOB', data_type,
                                                                        'NUMBER',decode(data_precision,null,data_type,data_type||'('||data_precision||')'), 
                                                                        decode( data_precision , null , dtc.data_type||'('||dtc.data_length||')', dtc.data_type||'('||DATA_PRECISION||')' )) ) 자료형태 ,
        decode(dtc.nullable,'N', 'NOT NULL')  "NOT NULL?",
        dtc.data_default DATA_DEFAULT
from DBA_COL_COMMENTS dcc, DBA_TAB_COLUMNS dtc , DBA_TAB_COMMENTS dt
where 1 =1
and dt.table_type in ('TABLE','VIEW')
and dcc.owner = 'TSUNIFY'
and dcc.table_name = 'METADATA_ESTM'
and dtc.owner = dcc.owner
and dtc.table_name = dcc.table_name
and dtc.column_name = dcc.column_name
and dtc.owner = dt.owner
and dtc.table_name = dt.table_name
--and dcc.comments is not null
;

------------------------------------------------------------------------------->



--------------------  변경 쿼리(070326 수정)----------------------------------------------
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(+)
      AND TBL.TABLE_NAME IN ('SCP_EM_MASTER', 'SCP_EM_HIST', 'SCP_EM_CODE', 'SCP_EM_CENTER')
order by TBL.TABLE_NAME , COLUMN_ID

--------------------------------------------------------------------------------------------->