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) || 
              CASE 
                  WHEN A.CHAR_USED = 'C' THEN ' CHAR' -- CHAR 단위
                  WHEN A.CHAR_USED = 'B' THEN ' BYTE' -- BYTE 단위
              END || ')' 
     END AS 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 A.DATA_TYPE LIKE 'TIMESTAMP%'
  AND A.OWNER = '스키마명'
  AND A.TABLE_NAME IN ('테이블명')
ORDER BY  A.TABLE_NAME, A.COLUMN_ID;