테이블,컬럼 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 골뱅이 네이트 닷컴