[Oracle] 테이블 정보조회

오라클 버전 : 11g

/* DB스키마 정보 조회 SQL */
SELECT
    A.TABLE_NAME   , A.TABLE_COMMENTS,
    A.COLUMN_NAME  , A.COLUMN_COMMENTS,
    A.DATA_TYPE    , A.DATA_LENGTH,
    MAX(A.PK_FLAG) AS PK_FLAG,
    MAX(A.FK_FLAG) AS FK_FLAG,
    A.NOT_NULL_FLAG, A.CHAR_USED
FROM(
    SELECT A1.TABLE_NAME TABLE_NAME 
         , replace(A1.TABLE_COMMENTS, chr(10), ' ') TABLE_COMMENTS  
         , A1.COLUMN_NAME COLUMN_NAME 
         , replace(A1.COLUMN_COMMENTS, chr(10), ' ') COLUMN_COMMENTS 
         , A1.DATA_TYPE 
         , A1.DATA_LENGTH 
         , (CASE 
               WHEN B1.CONSTRAINT_TYPE = 'P' 
                  THEN 'Y' 
            END) PK_FLAG 
         , (CASE 
               WHEN B1.CONSTRAINT_TYPE = 'R' 
                  THEN 'Y' 
            END) FK_FLAG 
         , A1.NOT_NULL_FLAG 
         , A1.CHAR_USED 
    FROM (SELECT B.COMMENTS TABLE_COMMENTS 
                 , A.COLUMN_ID 
                 , A.TABLE_NAME TABLE_NAME 
                 , C.COMMENTS COLUMN_COMMENTS 
                 , A.COLUMN_NAME COLUMN_NAME 
                 , (CASE A.NULLABLE 
                       WHEN 'Y' 
                          THEN 'N' 
                       else 'Y' 
                    END) NOT_NULL_FLAG 
                 , (CASE 
                       WHEN (A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 AND A.DATA_PRECISION IS NULL) 
                          THEN 'INTEGER' 
                       ELSE A.DATA_TYPE 
                    END)  as DATA_TYPE 
                 , (CASE 
                       WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2','NVARCHAR2','NCHAR','RAW') 
                          THEN '' || A.CHAR_LENGTH || '' 
                       WHEN (A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 AND A.DATA_PRECISION IS NULL) 
                          THEN '' 
                       WHEN (A.DATA_TYPE = 'FLOAT') 
                          THEN '' || A.DATA_PRECISION || '' 
                       WHEN (A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 ) 
                          THEN '' || A.DATA_PRECISION || '' 
                       WHEN (A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE <> 0) 
                          THEN '' || A.DATA_PRECISION || ',' || A.DATA_SCALE || '' 
                    END 
                   ) DATA_LENGTH 
                 , a.char_used 
              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 
                   )) A1 LEFT OUTER JOIN
          (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 
    ON A1.TABLE_NAME  = B1.TABLE_NAME  
    AND   A1.COLUMN_NAME = B1.COLUMN_NAME 
)A
GROUP BY A.TABLE_NAME, A.TABLE_COMMENTS, A.COLUMN_NAME, A.COLUMN_COMMENTS, A.DATA_TYPE, A.DATA_LENGTH, A.NOT_NULL_FLAG, A.CHAR_USED
order by A.TABLE_NAME, A.COLUMN_NAME