2015. 4. 21. 17:07



[SQL]

SELECT *
  FROM
      (
       SELECT A.OWNER
            , A.TABLE_NAME
            , B.COMMENTS AS TABLE_NAME_CO
            , C.COLUMN_ID
            , C.COLUMN_NAME
            , D.COMMENTS AS COLUMN_NAME_CO
            , C.DATA_TYPE || CASE WHEN C.DATA_TYPE IN ('VARCHAR2','CHAR')          THEN ' (' || C.CHAR_LENGTH || ' Byte)'
                                  WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE > 0 THEN ' (' || C.DATA_PRECISION ||','|| C.DATA_SCALE ||')'
                                  WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 THEN ' (' || C.DATA_PRECISION ||')'
                                  ELSE C.DATA_TYPE END AS DATA_TYPE
            , C.NULLABLE
            , (SELECT POSITION
                 FROM ALL_CONS_COLUMNS Y
                    , ALL_CONSTRAINTS Z
                WHERE Y.OWNER           = Z.OWNER
                  AND Y.TABLE_NAME      = Z.TABLE_NAME
                  AND Y.CONSTRAINT_NAME = Z.CONSTRAINT_NAME
                  AND Z.CONSTRAINT_TYPE = 'P'
                  AND Y.OWNER           = A.OWNER
                  AND Y.TABLE_NAME      = A.TABLE_NAME
                  AND Y.COLUMN_NAME     = C.COLUMN_NAME) AS COL_PK
            , C.DATA_DEFAULT AS COL_DEFAULT
         FROM ALL_TABLES       A
            , ALL_TAB_COMMENTS B
            , ALL_TAB_COLUMNS  C
            , ALL_COL_COMMENTS D
        WHERE A.OWNER       = B.OWNER
          AND A.TABLE_NAME  = B.TABLE_NAME
            AND C.OWNER       = A.OWNER
          AND C.TABLE_NAME  = A.TABLE_NAME
          AND D.OWNER       = A.OWNER
          AND D.TABLE_NAME  = A.TABLE_NAME
          AND D.COLUMN_NAME = C.COLUMN_NAME
      )
 WHERE OWNER LIKE '%'
   AND TABLE_NAME LIKE '%'
 ORDER BY TABLE_NAME, COLUMN_ID



엑셀 문서작업 한다고 필요해서 작업했던 것인데 나중에 또 써먹을 것 같아서 보관...



Posted by 해비