Table defination generator query for Oracle

table_def_1.sql

SELECT   A1.TABLE_COMMENTS
       , A1.TABLE_NAME
--     , A1.COLUMN_ID
       , A1.COLUMN_NAME AS COLUMN_ID
       , A1.COLUMN_COMMENTS AS COLUMN_NAME
       , A1.DATA_TYPE AS DATA_TYPE     
       , (CASE A1.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(A1.DATA_LENGTH)
                            WHEN 'DATE'   THEN ' '
                            ELSE TO_CHAR(A1.DATA_LENGTH) END) AS DATA_LENGTH
       , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG                              
       , (CASE WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'PK' END) PK_FLAG
FROM (SELECT B.COMMENTS TABLE_COMMENTS
            , A.TABLE_NAME TABLE_NAME
            , C.COMMENTS COLUMN_COMMENTS
            , A.COLUMN_NAME COLUMN_NAME
            , (CASE A.NULLABLE WHEN 'Y' THEN 'Y' END) NULL_FLAG
            , A.DATA_TYPE DATA_TYPE
            , A.DATA_LENGTH 
            , A.COLUMN_ID AS COLUMN_ID
            , A.DATA_PRECISION
   FROM USER_TAB_COLUMNS A
           , USER_TAB_COMMENTS B
           , USER_COL_COMMENTS C
         WHERE (A.TABLE_NAME = B.TABLE_NAME)
             AND (      A.TABLE_NAME = C.TABLE_NAME
                    AND A.COLUMN_NAME = C.COLUMN_NAME
                    )
             AND B.TABLE_TYPE = 'TABLE') A1
       , (SELECT A.TABLE_NAME
                     , A.COLUMN_NAME
                     , B.CONSTRAINT_TYPE
             FROM USER_CONS_COLUMNS A
                     , USER_CONSTRAINTS B
           WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
                AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE (    A1.TABLE_NAME = B1.TABLE_NAME(+)
 AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
--AND A1.TABLE_NAME LIKE 'VMS%'
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID

table_def_2.sql

-- 가장 많이 사용하는 경우.

SELECT A1.TABLE_COMMENTS
           , A1.TABLE_NAME
           , A1.COLUMN_ID
           , A1.COLUMN_NAME
           , A1.COLUMN_COMMENTS
           , (CASE
              WHEN B1.CONSTRAINT_TYPE = 'P'
              THEN 'Y'
              END) PK_FLAG
           , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG 
           , A1.DATA_TYPE||
             (CASE A1.DATA_TYPE 
              WHEN 'NUMBER'
              THEN '('||TO_CHAR(A1.DATA_LENGTH)||','||TO_CHAR(A1.DATA_PRECISION)||')'
              WHEN 'DATE'   THEN ' '
              ELSE '('||A1.DATA_LENGTH||')'
              END) DATA_TYPE 
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
                        , A.TABLE_NAME TABLE_NAME
                        , C.COMMENTS COLUMN_COMMENTS
                        , A.COLUMN_NAME COLUMN_NAME
                        , (CASE A.NULLABLE
                           WHEN 'Y'
                           THEN 'Y'
                           END) NULL_FLAG
                        , A.DATA_TYPE DATA_TYPE
                        , A.DATA_LENGTH 
                        , A.COLUMN_ID AS COLUMN_ID
                        , A.DATA_PRECISION
                FROM USER_TAB_COLUMNS A
                        , USER_TAB_COMMENTS B
                        , USER_COL_COMMENTS C
              WHERE (A.TABLE_NAME = B.TABLE_NAME)
                  AND (A.TABLE_NAME = C.TABLE_NAME
                          AND A.COLUMN_NAME = C.COLUMN_NAME
                          )
                  AND B.TABLE_TYPE = 'TABLE') A1
         , (SELECT A.TABLE_NAME
                       , A.COLUMN_NAME
                       , B.CONSTRAINT_TYPE
              FROM USER_CONS_COLUMNS A
                      , USER_CONSTRAINTS B
            WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
                AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE ( A1.TABLE_NAME = B1.TABLE_NAME(+)
     AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
  --AND A1.TABLE_NAME LIKE 'VMS%'
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID

tables_with_size.sql

SELECT TA1.TABLE_NAME AS TALBE_ID, 
       TA1.COMMENTS AS TABLE_NAME, 
       TA2.DATA_LENGTH AS DATA_LENGTH,
       TA3.MAX_NUM AS MAX_NUM,
       TA3.INITIAL_NUM AS INITIAL_NUM,
       (TA2.DATA_LENGTH*TA3.MAX_NUM) AS EXTEND_NUM
  FROM
       (SELECT  TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE') TA1,
       (SELECT  TABLE_NAME, SUM(DATA_LENGTH) AS DATA_LENGTH FROM USER_TAB_COLUMNS GROUP BY TABLE_NAME) TA2,
       (SELECT  TABLE_NAME
        ,(CASE
              WHEN (NUM_ROWS < 1000) THEN 1000
              WHEN (10000   > NUM_ROWS) AND (NUM_ROWS > 1000)   THEN 10000
              WHEN (100000  > NUM_ROWS) AND (NUM_ROWS > 10000)  THEN 100000
              WHEN (1000000 > NUM_ROWS) AND (NUM_ROWS > 100000) THEN 1000000
              ELSE 10000000
          END    ) AS MAX_NUM
        , INITIAL_EXTENT AS INITIAL_NUM
        , GREATEST(4, CEIL(NUM_ROWS / 
                     DECODE(((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) / 
                                    DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2,0,1,
                        ((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) / 
                                    DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2)) * 2)
          AS TABLESIZE_KBYTES
           FROM  USER_TABLES) TA3
WHERE TA1.TABLE_NAME = TA2.TABLE_NAME
  AND TA1.TABLE_NAME = TA3.TABLE_NAME 
ORDER BY TA1.TABLE_NAME