[TIP] 테이블 레이아웃 조회

/* 익숙하지 않은 테이블 레이아웃을 보기 편하게 표시해주는 쿼리입니다. */

 

SELECT A.OWNER, A.TABLE_NAME, A.COMMENTS TABLE_COMMENTS
     , B.COLUMN_ID, B.COLUMN_NAME, C.COMMENTS COLUMN_COMMENTS
     , B.DATA_TYPE||CASE WHEN B.DATA_TYPE = 'TIMESTAMP(6)' THEN ''
                         ELSE '('||CASE WHEN B.DATA_TYPE IN ('NUMBER', 'FLOAT')
                                             AND B.DATA_PRECISION IS NOT NULL
                                             AND NVL(B.DATA_SCALE, 0) <> 0 THEN TO_CHAR(B.DATA_PRECISION)||','||TO_CHAR(B.DATA_SCALE)
                                        WHEN B.DATA_TYPE IN ('NUMBER', 'FLOAT')
                                             AND B.DATA_PRECISION IS NOT NULL
                                             AND NVL(B.DATA_SCALE, 0) = 0 THEN TO_CHAR(B.DATA_PRECISION)
                                   ELSE TO_CHAR(B.DATA_LENGTH) END||')' END AS DATA_TYPE
     , B.NULLABLE
FROM   ALL_TAB_COMMENTS A
INNER JOIN ALL_TAB_COLUMNS B
       ON     A.OWNER = B.OWNER
       AND    A.TABLE_NAME = B.TABLE_NAME
INNER JOIN ALL_COL_COMMENTS C
       ON     B.OWNER = C.OWNER
       AND    B.TABLE_NAME  = C.TABLE_NAME
       AND    B.COLUMN_NAME = C.COLUMN_NAME
WHERE 1=1
AND    A.OWNER = 'SCOTT'
AND    A.TABLE_NAME = 'EMP'
ORDER BY A.OWNER, A.TABLE_NAME, B.COLUMN_ID;