티스토리 뷰

웹개발이야기/DataBase

ORACLE 데이터 명세서 출력

최고다님 2016. 12. 9. 16:43

프로젝트 막바지가 되어간다.. 산출물이 필요하시다고 말하신다..

토드에서도 뽑을 수 있지만 html 한장에 모든 테이블 및 컬럼 정보가 필요하다고 하신다..

뭐 어쩔 수 없지.. 그냥 쿼리 짜서 한번에 뽑았다.


재능 기부합니다.. 필요하신 분 가져가세요....


SELECT TR

FROM (


    SELECT '0' AS TABLE_NAME,

        '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html lang="ko"> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"> <title></title><style>table { border-collapse: collapse; } table, th, td { border: 1px solid black; } th { background-color: gray; } hr { margin-bottom: 30px; visibility: hidden;}</style> </head> <body> ' AS TR,

        0 AS ORD

    FROM DUAL

    

    UNION ALL

    

    SELECT TC.TABLE_NAME AS TABLE_NAME,

        '<table style="width: 100%;"><tbody>' AS TR,

        1 AS ORD

    FROM TABS T

    INNER JOIN ALL_TAB_COMMENTS TC ON T.TABLE_NAME = TC.TABLE_NAME

    

    UNION ALL

    

    SELECT TC.TABLE_NAME AS TABLE_NAME,

        '<tr><th>엔티티 ID</th><td colspan="4">'||TC.TABLE_NAME||'</td><th>엔티티 명</th><td colspan="4">'||TC.COMMENTS||'</td></tr><tr><th>관련 클래스 ID</th><td colspan="4"></td><th>관련 클래스 명</th><td colspan="4"></td></tr><tr><th>엔티티 설명</th><td colspan="9">'||TC.COMMENTS||'</td></tr><tr><th>속성명</th><th>동의어</th><th>타입</th><th>길이</th><th>NOT NULL</th><th>PK</th><th>FK</th><th>INX</th><th>제약조건</th><th>비고</th></tr>' AS TR,

        2 AS ORD

    FROM TABS T

    INNER JOIN ALL_TAB_COMMENTS TC ON T.TABLE_NAME = TC.TABLE_NAME


    UNION ALL


    SELECT

        C.TABLE_NAME AS TABLE_NAME,

        '<tr><td>'|| C.COLUMN_NAME ||'</td><td>'|| CC.COMMENTS ||'</td><td>'|| C.DATA_TYPE ||'</td><td>'|| C.DATA_LENGTH ||'</td><td>'|| (CASE WHEN C.NULLABLE = 'Y' THEN 'N' ELSE 'Y' END) ||'</td><td>'|| (CASE WHEN NVL(PK.PK_CNT, 0) > 0 THEN 'O' ELSE NULL END) ||'</td><td>'|| (CASE WHEN NVL(FK.FK_CNT, 0) > 0 THEN 'O' ELSE NULL END) ||'</td><td></td><td></td><td></td></tr>' AS TR,

        RANK() OVER(PARTITION BY C.TABLE_NAME ORDER BY C.TABLE_NAME, C.COLUMN_ID) + 2 AS ORD

        /*

        C.COLUMN_NAME AS "속성명",

        CC.COMMENTS AS "동의어",

        C.DATA_TYPE AS "타입",

        C.DATA_LENGTH AS "길이",

        CASE WHEN C.NULLABLE = 'Y' THEN 'N' ELSE 'Y' END AS "NOT NULL",

        CASE WHEN NVL(PK.PK_CNT, 0) > 0 THEN 'O' ELSE NULL END AS PK,

        CASE WHEN NVL(FK.FK_CNT, 0) > 0 THEN 'O' ELSE NULL END AS FK,

        --CASE WHEN NVL(IDX.IDX_CNT, 0) > 0 THEN 'O' ELSE NULL END AS IDX,

        CASE WHEN NVL(UK.UK_CNT, 0) > 0 THEN 'O' ELSE NULL END AS UK

        */

    FROM TABS T

    INNER JOIN ALL_TAB_COMMENTS TC ON T.TABLE_NAME = TC.TABLE_NAME

    INNER JOIN COLS C ON T.TABLE_NAME = C.TABLE_NAME

    INNER JOIN ALL_COL_COMMENTS CC ON C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME

    LEFT OUTER JOIN (

        SELECT CM.TABLE_NAME, CC.COLUMN_NAME, COUNT(*) AS PK_CNT

        FROM USER_CONSTRAINTS CM

        INNER JOIN USER_CONS_COLUMNS CC ON CM.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

        WHERE CM.CONSTRAINT_TYPE = 'P'

        GROUP BY CM.TABLE_NAME, CC.COLUMN_NAME

    ) PK ON PK.TABLE_NAME = C.TABLE_NAME AND PK.COLUMN_NAME = C.COLUMN_NAME

    LEFT OUTER JOIN (

        SELECT CM.TABLE_NAME, CC.COLUMN_NAME, COUNT(*) AS FK_CNT

        FROM USER_CONSTRAINTS CM

        INNER JOIN USER_CONS_COLUMNS CC ON CM.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

        WHERE CM.CONSTRAINT_TYPE = 'R'

        GROUP BY CM.TABLE_NAME, CC.COLUMN_NAME

    ) fK ON FK.TABLE_NAME = C.TABLE_NAME AND FK.COLUMN_NAME = C.COLUMN_NAME

    /*

    LEFT OUTER JOIN (

        SELECT CM.TABLE_NAME, CC.COLUMN_NAME, COUNT(*) AS IDX_CNT

        FROM USER_INDEXES CM

        INNER JOIN USER_IND_COLUMNS CC ON CM.INDEX_NAME = CC.INDEX_NAME

        GROUP BY CM.TABLE_NAME, CC.COLUMN_NAME

    ) IDX ON IDX.TABLE_NAME = C.TABLE_NAME AND IDX.COLUMN_NAME = C.COLUMN_NAME

    */

    LEFT OUTER JOIN (

        SELECT CM.TABLE_NAME, CC.COLUMN_NAME, COUNT(*) AS UK_CNT

        FROM USER_CONSTRAINTS CM

        INNER JOIN USER_CONS_COLUMNS CC ON CM.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

        WHERE CM.CONSTRAINT_TYPE = 'U'

        GROUP BY CM.TABLE_NAME, CC.COLUMN_NAME

    ) UK ON UK.TABLE_NAME = C.TABLE_NAME AND UK.COLUMN_NAME = C.COLUMN_NAME

    --ORDER BY C.TABLE_NAME, C.COLUMN_ID

    

    UNION ALL

    

    SELECT TC.TABLE_NAME AS TABLE_NAME,

        '</tbody></table><hr/>' AS TR,

        998 AS ORD

    FROM TABS T

    INNER JOIN ALL_TAB_COMMENTS TC ON T.TABLE_NAME = TC.TABLE_NAME

    

    UNION ALL

    

    SELECT 'ZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS TABLE_NAME,

        '</body></html>' AS TR,

        999 AS ORD

    FROM DUAL

)

ORDER BY TABLE_NAME, ORD



'웹개발이야기 > DataBase' 카테고리의 다른 글

[MariaDB] 데이터 변경 시 변경 시간 UPDATE  (0) 2021.07.09
오라클 시스템 테이블들  (0) 2016.09.08
오라클 비밀번호 만료 관련 설정  (0) 2016.01.25
[Oracle] 테이블 또는 프로시져 복원하기  (0) 2012.03.27
[Oracle] Connect By  (0) 2011.03.21
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
TAG more
«   2024/05   »
일 월 화 수 목 금 토
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
글 보관함