테이블,컬럼 comments (정보관리테이블만들기)
< 정보테이블 생성하기 >
---------------------------------------------------------------------
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)
)
TABLESPACE TS_COM_TB
;
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)
USING INDEX
TABLESPACE TS_COM_IX ) ;
GRANT INSERT, UPDATE, DELETE ON TB_INFO TO ROLE_COM_W;
GRANT SELECT ON TB_INFO TO ROLE_COM_R;
CREATE PUBLIC SYNONYM TB_INFO FOR COM.TB_INFO;
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(+)
and a.comments is not null) 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_length||')'
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 골뱅이 네이트 닷컴
---------------------------------------------------------------------
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)
)
TABLESPACE TS_COM_TB
;
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)
USING INDEX
TABLESPACE TS_COM_IX ) ;
GRANT INSERT, UPDATE, DELETE ON TB_INFO TO ROLE_COM_W;
GRANT SELECT ON TB_INFO TO ROLE_COM_R;
CREATE PUBLIC SYNONYM TB_INFO FOR COM.TB_INFO;
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(+)
and a.comments is not null) 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_length||')'
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 골뱅이 네이트 닷컴
[이 게시물은 관리자님에 의해 2011-10-31 16:47:36 Oracle에서 이동 됨]
|
댓글을 작성하시려면 로그인이 필요합니다.