*******************在PB下拉取数据库注释****************

// 将ORACLE数据库的注释同步到PB中   代码开始
DELETE FROM PBCATCOL WHERE PBC_TNAM       LIKE '%';
DELETE FROM PBCATTBL WHERE PBT_TNAM       LIKE '%';

 
 INSERT INTO PBCATTBL 
         ( PBT_TNAM,  
           PBT_OWNR ,
           PBT_CMNT) 
    SELECT ALL_TAB_COMMENTS.TABLE_NAME,  
           ALL_TAB_COMMENTS.OWNER,  
           ALL_TAB_COMMENTS.COMMENTS 
      FROM ALL_TAB_COMMENTS 
     WHERE ALL_TAB_COMMENTS.OWNER = 'ORCL'
       AND TABLE_NAME LIKE '%';

// 同步字段名

 INSERT INTO PBCATCOL 
         ( PBC_TNAM,  
           PBC_OWNR,  
           PBC_CNAM,  
           PBC_LABL,
          PBC_CMNT,
          PBC_HDR) 
     SELECT ALL_COL_COMMENTS.TABLE_NAME,
     ALL_COL_COMMENTS.OWNER,  
            ALL_COL_COMMENTS.COLUMN_NAME,  
            ALL_COL_COMMENTS.COMMENTS  ,
            ALL_COL_COMMENTS.COMMENTS ,
            ALL_COL_COMMENTS.COMMENTS
       FROM ALL_COL_COMMENTS 
      WHERE ALL_COL_COMMENTS.OWNER = 'ORCL'
       AND TABLE_NAME LIKE '%';

 COMMIT WORK;

 

 

=================================20100608=============================

 

BEGIN
   -- PB中表名字段名同步
   -- 以TEST为例
   -- 同步表名
   DELETE FROM pbcattbl
    WHERE pbt_ownr = 'YNSYL';

   INSERT INTO pbcattbl
        ( pbt_tnam,
          pbt_ownr,
          pbt_cmnt )
   SELECT table_name,
          owner,
          comments
     FROM all_tab_comments
    WHERE owner = 'YNSYL';

   -- 同步字段名
   DELETE FROM pbcatcol
    WHERE pbc_ownr = 'YNSYL';

   INSERT INTO pbcatcol
        ( pbc_tnam,
          pbc_ownr,
          pbc_cnam,
          pbc_labl,
          pbc_cmnt,
          pbc_hdr  )
   SELECT table_name,
          owner,
          column_name,
          comments,
          comments,
          comments
     FROM all_col_comments
    WHERE owner = 'YNSYL';

   COMMIT;
END;

 

 

 

 

 

 

==========IMPORTANT===========

 

   DELETE FROM pbcattbl
    WHERE pbt_ownr = 'dba';

   INSERT INTO pbcattbl
        ( pbt_tnam,
          pbt_ownr,
          pbt_cmnt )
   SELECT table_name,
          'dba',
          remarks
     FROM sys.systable
    WHERE remarks is not null;

   DELETE FROM pbcatcol
    WHERE pbc_ownr = 'dba';

   INSERT INTO pbcatcol
        ( pbc_tnam,
          pbc_ownr,
          pbc_cnam,
          pbc_labl,
          pbc_cmnt,
          pbc_hdr  )
   SELECT tname,
          'dba',
          cname,
          remarks,
          remarks,
          remarks
     FROM sys.syscolumns
    WHERE remarks is not null
      and creator = 'DBA';

   COMMIT work;

 

PB里边有两张表，一张是pbcattbl：表注释，一张是pbcatcol，列注释
刚才那个语句的意思就是从数据库注释信息里提取数据，写入PB的表里
这样使用PB查看表的时候就可以很直观的看见含义
不过有个前提，就是在数据库建表的时候是带注释的