티스토리 뷰

    SELECT
         COLUMN_ID AS SEQ
        ,( SELECT NVL(D.POSITION, '')
              FROM ALL_CONS_COLUMNS D
                 , all_constraints E
             WHERE D.OWNER           = UPPER(:user_name)
               AND D.OWNER           = A.OWNER
               AND D.TABLE_NAME      = A.TABLE_NAME
               AND D.COLUMN_NAME     = A.COLUMN_NAME
               AND D.OWNER           = E.OWNER
               AND D.TABLE_NAME      = E.TABLE_NAME
               AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
               AND E.CONSTRAINT_TYPE = 'P') AS PK
        ,( SELECT CASE WHEN D.POSITION is null then ''
                       ELSE 'V'
                   END 
              FROM ALL_CONS_COLUMNS D
                 , all_constraints E
             WHERE D.OWNER           = UPPER(:user_name)
               AND D.OWNER           = A.OWNER
               AND D.TABLE_NAME      = A.TABLE_NAME
               AND D.COLUMN_NAME     = A.COLUMN_NAME
               AND D.OWNER           = E.OWNER
               AND D.TABLE_NAME      = E.TABLE_NAME
               AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
               AND E.CONSTRAINT_TYPE = 'R') AS FK                  
         , A.COLUMN_NAME AS COLUMN_ID
         , C.COMMENTS
         , A.DATA_TYPE
         , A.DATA_LENGTH
         , A.NULLABLE
         , A.DATA_DEFAULT
      FROM ALL_TAB_COLUMNS  A
         , ALL_TAB_COMMENTS B
         , ALL_COL_COMMENTS C
        WHERE A.OWNER = UPPER(:user_name)
        AND B.OWNER = UPPER(:user_name)
        AND C.OWNER = UPPER(:user_name)
        AND A.OWNER = B.OWNER
        AND A.OWNER = C.OWNER
        AND A.TABLE_NAME  = B.TABLE_NAME
        AND A.TABLE_NAME  = C.TABLE_NAME
        AND A.COLUMN_NAME = C.COLUMN_NAME
        AND A.TABLE_NAME = UPPER(:table_name)
        ORDER BY 1

토드나 SQL 게이트에서 쿼리를 실행하고 user_name 파라미터에 해당 DB의 유저명을 입력하고 table_name 파라미터에 테이블 정보를 긁어올 테이블명을 입력하면 질의문으로 조회해 올 수 있다.

SQL 게이트에 테이블 레포트라는 기능이 있어서 위의 질의문이 필요없을지도 모르겠다.
댓글
댓글쓰기 폼