테이블, 칼럼 조회

테이블 조회

    SELECT MCOMT.COMMENTS
         , MTABL.*
      FROM USER_TABLES MTABL
         , USER_TAB_COMMENTS MCOMT
     WHERE MTABL.TABLE_NAME = MCOMT.TABLE_NAME4
       AND (UPPER(COMMENTS) LIKE '%' || UPPER('') || '%'
         OR UPPER(MTABL.TABLE_NAME) LIKE '%' || UPPER(''}) || '%')
     ORDER BY
           MCOMT.COMMENTS
         , MTABL.TABLE_NAME

칼럼 조회

    SELECT MCOLS.TABLE_NAME
         , MCOLS.COLUMN_NAME
         , MCOLS.DATA_TYPE
         , MCOLS.DATA_LENGTH
         , MCOLS.NULLABLE
         , CTABS.COMMENTS TAB_NAME
         , CCOLS.COMMENTS COL_NAME
      FROM USER_TAB_COLUMNS MCOLS
         , USER_TAB_COMMENTS CTABS
         , USER_COL_COMMENTS CCOLS
     WHERE MCOLS.TABLE_NAME = CTABS.TABLE_NAME
       AND MCOLS.TABLE_NAME = CCOLS.TABLE_NAME
       AND MCOLS.COLUMN_NAME = CCOLS.COLUMN_NAME
       AND (MCOLS.TABLE_NAME LIKE '%' || UPPER('') || '%' OR CTABS.COMMENTS LIKE '%' || UPPER('') || '%')
       AND (MCOLS.COLUMN_NAME LIKE '%' || UPPER('') || '%' OR CCOLS.COMMENTS LIKE '%' || UPPER('') || '%')
     ORDER BY
           CCOLS.COMMENTS
         , CTABS.COMMENTS
         , CTABS.TABLE_NAME

칼럼(PK) 조회

SELECT MCOLS.TABLE_NAME
     , CTABS.COMMENTS TABLE_TEXT
     , MCOLS.COLUMN_ID
     , DECODE(VKEYS.COLUMN_NAME, NULL, '', 'PK') AS PK_YN
     , MCOLS.COLUMN_NAME
     , CCOLS.COMMENTS COLUMN_TEXT
     , MCOLS.DATA_TYPE
     , MCOLS.DATA_LENGTH
  FROM USER_TAB_COLUMNS MCOLS
     , USER_TAB_COMMENTS CTABS
     , USER_COL_COMMENTS CCOLS
     , (
             SELECT DISTINCT DKEYS.COLUMN_NAME
                  FROM USER_CONSTRAINTS MKEYS
                     , USER_CONS_COLUMNS DKEYS
                 WHERE MKEYS.TABLE_NAME = DKEYS.TABLE_NAME
                   AND MKEYS.TABLE_NAME = '테이블명'
     ) VKEYS
 WHERE MCOLS.TABLE_NAME = CTABS.TABLE_NAME (+)
   AND MCOLS.TABLE_NAME = CCOLS.TABLE_NAME (+)
   AND MCOLS.COLUMN_NAME = CCOLS.COLUMN_NAME (+)
   AND MCOLS.COLUMN_NAME = VKEYS.COLUMN_NAME (+)
   AND MCOLS.TABLE_NAME = '테이블명'
 ORDER BY
       MCOLS.COLUMN_ID