테이블 레이아웃 추출

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;

출처: 꼼수모음