SELECT     tbl.OWNER "OWNER"
         , tcmt.COMMENTS "엔티티명"
         , tbl.TABLE_NAME "테이블명"
         , ccmt.COMMENTS "어트리뷰트명"
         , cols.COLUMN_NAME "컬럼명"
         , cols.DATA_TYPE "타입"
         , CASE
                WHEN cols.DATA_TYPE = 'DATE'
                     OR cols.DATA_TYPE LIKE '%LOB%'
                    THEN ''
                ELSE TO_CHAR(cols.DATA_LENGTH)
           END
           ||
           CASE
                WHEN cols.DATA_SCALE > 0
                    THEN ',' || cols.DATA_SCALE
                ELSE ''
           END "길이"
         , cols.NULLABLE "Null 여부"
         , CASE
               WHEN
               (
                   SELECT  con_col.COLUMN_NAME
                   FROM    ALL_CONSTRAINTS cons
                       INNER JOIN
                           ALL_CONS_COLUMNS con_col
                       ON  cons.OWNER           = con_col.OWNER
                       AND cons.CONSTRAINT_NAME = con_col.CONSTRAINT_NAME
                       AND cons.TABLE_NAME      = cons.TABLE_NAME
                   WHERE   cons.OWNER           = tbl.OWNER
                   AND     cons.TABLE_NAME      = tbl.TABLE_NAME
                   AND     cons.CONSTRAINT_TYPE = 'P'
                   AND     con_col.COLUMN_NAME  = cols.COLUMN_NAME
               )
               IS NOT NULL
                   THEN 'Y'
               ELSE ''
           END "PK 여부"
FROM    ALL_TABLES tbl
    INNER JOIN
        ALL_TAB_COMMENTS tcmt
    ON  tbl.OWNER      = tcmt.OWNER
    AND tbl.TABLE_NAME = tcmt.TABLE_NAME
    INNER JOIN
        ALL_TAB_COLUMNS cols
    ON  tbl.OWNER      = cols.OWNER
    AND tbl.TABLE_NAME = cols.TABLE_NAME
    INNER JOIN
        ALL_COL_COMMENTS ccmt
    ON  cols.OWNER       = ccmt.OWNER
    AND cols.TABLE_NAME  = ccmt.TABLE_NAME
    AND cols.COLUMN_NAME = ccmt.COLUMN_NAME
WHERE    tbl.OWNER = 'OWNER 명'
AND      tbl.TABLE_NAME IN ('테이블 영문명1', '테이블 영문명2')
--AND    tcmt.COMMENTS LIKE '%테이블 한글명%'
--AND    ccmt.COMMENTS LIKE '%컬럼 한글명%'
--AND    cols.COLUMN_NAME LIKE '%컬럼영문명%'
ORDER BY
           tbl.OWNER
         , tbl.TABLE_NAME
         , cols.COLUMN_ID

 

profile

이브리타, 나의 에뜨와르
너와 내가 공유하는 추억
너와 내가 만들 추억