Oracle DBMS 테이블 명세서 추출 하기

Oracle DBMS 테이블 명세서 추출 하기

 

SELECT
  A.TABLE_NAME,
  A.COLUMN_ID,       -- 컬럼순서
  B.COMMENTS,        -- 컬럼코멘트 
  A.COLUMN_NAME,     -- 컬럼명 
  A.DATA_TYPE ||
     case when a.data_type = 'NUMBER' then 
            case  when a.DATA_PRECISION is null then ''
                  when a.data_scale = 0 then '('||to_char(a.data_precision)  ||')'
                  when a.data_scale >=0 then '('||to_char(a.data_precision)||','||to_char(a.data_scale) ||')' else  '('||to_char(a.data_length)||')'end 
         when a.data_type like 'TIMESTAMP%' then '' 
     else '('||to_char(a.data_length)||')' end dat_len,
  A.NULLABLE,        -- NULLABLE
  A.DATA_DEFAULT,     -- 디폴트값
  a.data_length,
  a.data_precision,
  A.DATA_SCALE  
FROM 
  DBA_TAB_COLUMNS A, DBA_COL_COMMENTS B
WHERE 1=1
  AND A.TABLE_NAME = B.TABLE_NAME 
  AND A.COLUMN_NAME = B.COLUMN_NAME
  --and  DATA_TYPE like 'TIMESTAMP%'
  and  a.owner='스키마명'
  and a.table_name in('테이블명')
ORDER BY  A.TABLE_NAME, A.COLUMN_ID;