< 정보테이블 생성하기 >
---------------------------------------------------------------------
DROP TABLE TB_INFO;

CREATE TABLE TB_INFO (
OWNER VARCHAR(15) NOT NULL,
TABLE_NAME VARCHAR(30) NOT NULL,
TABLE_COMMENTS VARCHAR(200) ,
COLUMN_NAME VARCHAR(30) NOT NULL,
COLUMN_COMMENTS VARCHAR(200) ,
COLUMN_ID NUMBER,
DATA_TYPE VARCHAR(20) NOT NULL,
NULLABLE CHAR(1) NOT NULL,
TABLE_TYPE VARCHAR(20)
)
;

COMMENT ON COLUMN TB_INFO.OWNER IS "OWNER";
COMMENT ON COLUMN TB_INFO.TABLE_NAME IS "테이블명";
COMMENT ON COLUMN TB_INFO.TABLE_COMMENTS IS "테이블설명";
COMMENT ON COLUMN TB_INFO.COLUMN_NAME IS "컬럼명";
COMMENT ON COLUMN TB_INFO.COLUMN_COMMENTS IS "컬럼설명";
COMMENT ON COLUMN TB_INFO.COLUMN_ID IS "컬럼ID";
COMMENT ON COLUMN TB_INFO.DATA_TYPE IS "데이터타입";
COMMENT ON COLUMN TB_INFO.NULLABLE IS "NULL허용여부";
COMMENT ON COLUMN TB_INFO.TABLE_TYPE IS "테이블형태";

ALTER TABLE TB_INFO
ADD ( CONSTRAINT PK_INFO PRIMARY KEY (OWNER, TABLE_NAME,COLUMN_NAME)
) ;

commit;
---------------------------------------------------------------------



< 실제 테이블에 테이블정보 넣기 >

---------------------------------------------------------------------

DELETE
FROM TB_INFO
WHERE OWNER =
(select distinct(user) from user_tables);

INSERT INTO TB_INFO
select TB_COM.OWNER,
TB_COM.TABLE_NAME,
TB_COM.TCOMMENTS,
TB_COM.COLUMN_NAME,
TB_COM.CCOMMENTS,
TB_COL.COLUMN_ID,
TB_COL.DATA_TYPE,
TB_COL.NULLABLE,
TB_COM.TABLE_TYPE
FROM
(select user as OWNER,
a.TABLE_NAME as TABLE_NAME,
a.COMMENTS as TCOMMENTS,
b.COLUMN_NAME as COLUMN_NAME,
b.COMMENTS as CCOMMENTS,
a.TABLE_TYPE as TABLE_TYPE
from user_tab_comments a, user_col_comments b
where
a.table_name = b.table_name(+) ) TB_COM,
(select table_name, column_name, nullable, column_id,
(CASE WHEN data_precision is null THEN data_type||"("||data_length||")"
WHEN data_scale = 0 THEN data_type||"("||data_precision||")"
ELSE data_type||"("||data_precision||"."||data_scale||")"
END) as data_type
from user_tab_columns) TB_COL
WHERE TB_COM.TABLE_NAME = TB_COL.TABLE_NAME
and TB_COM.COLUMN_NAME = TB_COL.COLUMN_NAME;

commit;
---------------------------------------------------------------------
위 내용으로 작성되어진 예시 캡쳐화면을 첨부합니다.

위 예제는 테이블명,테이블스페이스명,롤,시너님을 수정하셔서
실제 프로젝트에서 사용하는 쿼리이기에 환경만 만드시거나 조정만 하시면 충분히 사용하실듯 하네요.

^^ 오랫만에 접속해보는군요. 다시 자주 찾아 뵙도록 하겠습니다.

edit512 골뱅이 네이트 닷컴