테이블,컬럼 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에서 이동 됨]
|
댓글을 작성하시려면 로그인이 필요합니다.
프로그램
태그 필터 (최대 3개)
전체
개발자
소스
기타
mysql
팁자료실
javascript
php
linux
flash
정규표현식
jquery
node.js
mobile
웹서버
os
프로그램
강좌
썸네일
이미지관련
도로명주소
그누보드5
기획자
견적서
계약서
기획서
마케팅
제안서
seo
통계
서식
통계자료
퍼블리셔
html
css
반응형
웹접근성
퍼블리싱
표준화
반응형웹
홈페이지기초
부트스트랩
angularjs
포럼
스크린리더
센스리더
개발자톡
개발자팁
퍼블리셔톡
퍼블리셔팁
기획자톡
기획자팁
프로그램강좌
퍼블리싱강좌