[ORACLE] 테이블정의서 만들기 쿼리

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 게이트에 테이블 레포트라는 기능이있어서 위의 질의문이 필요없을지도 모르겠다.