[ORACLE] 테이블 레이아웃 추출 수정....

기존 소스 수정바람...........

 

 

 

WITH V_INDEX AS
(
        SELECT  A.PNO
                , B.TABLE_NAME
                , B.COLUMN_NAME
                , B.COLUMN_POSITION
        FROM    (
                SELECT  ROW_NUMBER() OVER (PARTITION BY A.TABLE_NAME ORDER BY A.INDEX_NAME) AS PNO
                        , A.INDEX_NAME
                        , A.TABLE_NAME
                FROM    USER_INDEXES A
                WHERE   1=1
                AND     A.UNIQUENESS = 'NONUNIQUE'
                ) A
                , USER_IND_COLUMNS B
        WHERE   A.INDEX_NAME = B.INDEX_NAME
)
SELECT  A.TNAME                 AS TBLID
        , A.COLNO               AS COLNO
        , A.CNAME               AS COLID
        , C.COLUMN_POSITION     AS PK
        , A.COLTYPE             AS "TYPE"
        , DECODE(A.COLTYPE, 'NUMBER', TO_CHAR(A.PRECISION)||','||TO_CHAR(A.SCALE), TO_CHAR(A.WIDTH))  AS WIDTH
        , DECODE(A.NULLS, 'NULL', '', A.NULLS)  "NULLS"
        , A.DEFAULTVAL          AS "DEFAULT"
        , E.COLUMN_POSITION     AS "IX1"
        , F.COLUMN_POSITION     AS "IX2"
        , G.COLUMN_POSITION     AS "IX3"
FROM    COL A
        , USER_COL_COMMENTS B
        , (
        SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_POSITION
        FROM   USER_IND_COLUMNS A, USER_INDEXES B
        WHERE  A.INDEX_NAME = B.INDEX_NAME
        AND    B.UNIQUENESS = 'UNIQUE'
        ) C
        , USER_TAB_COMMENTS D
        , V_INDEX E
        , V_INDEX F
        , V_INDEX G
WHERE   A.TNAME = B.TABLE_NAME(+)
AND     A.CNAME = B.COLUMN_NAME(+)
AND     A.TNAME = C.TABLE_NAME(+)
AND     A.CNAME = C.COLUMN_NAME(+)
AND     A.TNAME = D.TABLE_NAME(+)
AND     A.TNAME = E.TABLE_NAME(+)
AND     A.CNAME = E.COLUMN_NAME(+)
AND     1       = E.PNO(+)
AND     A.TNAME = F.TABLE_NAME(+)
AND     A.CNAME = F.COLUMN_NAME(+)
AND     2       = F.PNO(+)
AND     A.TNAME = G.TABLE_NAME(+)
AND     A.CNAME = G.COLUMN_NAME(+)
AND     3       = G.PNO(+)
AND     A.TNAME NOT LIKE 'BIN$%'
--AND     (A.TNAME LIKE 'OA%' OR A.TNAME LIKE 'IF%' OR A.TNAME LIKE 'PB%')
AND     A.TNAME IN
        (
         ''
        )
ORDER BY D.TABLE_TYPE, A.TNAME, A.COLNO
;

select  *
from   USER_TAB_COMMENTS;