关于PowerDesigner 反向工程表名和列名没有注释的解决方案

我的PowerDesigner版本是12.0.0.1700,不同版本差异应该不大,道理是一样的 

生成结果如下:

1.列注释

原来代码：

{OWNER, TABLE, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, NOTNULL EX, DEFAULT, COMMENT, ExtNotNullConstraintName EX, ExtNotNullDeferOption EX}

select
   c.owner,
   c.table_name,
   c.column_name,
   decode(c.data_type_owner, NULL, replace(c.data_type, 'TIMESTAMP(6)', 'TIMESTAMP'), c.data_type_owner||'.'||c.data_type),
   decode(c.data_type_owner, NULL, decode(c.character_set_name, NULL, to_char(decode(c.data_type, 'RAW', c.data_length, c.data_precision)), to_char(c.char_col_decl_length) || decode(c.char_used, 'C', ' char', '')), NULL),
   c.data_precision,
   decode(c.data_precision, NULL, NULL, c.data_scale),
   decode(c.nullable, 'N', 'NOT NULL', [%ISDBAUSER%?'%SqlGetNotNullConstName.'||c.owner||c.table_name||c.column_name||'nullable%':'']),
   c.data_default,
   m.comments,   
   [%ISDBAUSER%?'%SqlGetNotNullConstName.'||c.owner||c.table_name||c.column_name||'%':''],
   [%ISDBAUSER%?'%SqlGetNotNullConstName.'||c.owner||c.table_name||c.column_name||'state%':'']
from
   sys.all_tab_columns c,
   sys.all_col_comments m
where
   c.table_name = %.q:TABLE%
   and c.table_name = m.table_name
   and c.column_name = m.column_name
   and c.owner = m.owner
[  and c.owner=%.q:SCHEMA%]
order by
   c.owner, c.table_name, c.column_id

解决方案：      

在PowerDesigner的 tools --> resources --> dbms --> 双击oracle version 10g 打开DBMS属性窗口，在general选项卡中选择script-->objects-->column-->SQLlistquery修改其中的内容为:

注:红色加粗是新增加的COLNNAME,   m.comments,

{OWNER, TABLE, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, NOTNULL EX, DEFAULT, COMMENT, COLNNAME, ExtNotNullConstraintName EX, ExtNotNullDeferOption EX}

select
   c.owner,
   c.table_name,
   c.column_name,
   decode(c.data_type_owner, NULL, replace(c.data_type, 'TIMESTAMP(6)', 'TIMESTAMP'), c.data_type_owner||'.'||c.data_type),
   decode(c.data_type_owner, NULL, decode(c.character_set_name, NULL, to_char(decode(c.data_type, 'RAW', c.data_length, c.data_precision)), to_char(c.char_col_decl_length) || decode(c.char_used, 'C', ' char', '')), NULL),
   c.data_precision,
   decode(c.data_precision, NULL, NULL, c.data_scale),
   decode(c.nullable, 'N', 'NOT NULL', [%ISDBAUSER%?'%SqlGetNotNullConstName.'||c.owner||c.table_name||c.column_name||'nullable%':'']),
   c.data_default,
   m.comments,   
   m.comments,  
   [%ISDBAUSER%?'%SqlGetNotNullConstName.'||c.owner||c.table_name||c.column_name||'%':''],
   [%ISDBAUSER%?'%SqlGetNotNullConstName.'||c.owner||c.table_name||c.column_name||'state%':'']
from
   sys.all_tab_columns c,
   sys.all_col_comments m
where
   c.table_name = %.q:TABLE%
   and c.table_name = m.table_name
   and c.column_name = m.column_name
   and c.owner = m.owner
[  and c.owner=%.q:SCHEMA%]
order by
   c.owner, c.table_name, c.column_id

2.表注释

原代码

{OWNER, TABLE}

select
   t.owner,
   t.table_name
from
   sys.all_all_tables t
where
   not exists (select 1 from sys.all_mviews s where s.owner = t.owner and t.table_name in (s.mview_name, s.update_log))
   and t.iot_name is null
   and nvl(t.dropped, 'NO') = 'NO'
[  and t.table_name=%.q:TABLE%]
[  and t.owner=%.q:SCHEMA%]
order by
   t.owner, t.table_name

解决方案:

在PowerDesigner的tools --> resources --> dbms --> 双击oracle version 10g 打开DBMS属性窗口,在general 选项卡中选择script --> objects --> table --> SqlListQuery 修改其中的内容为:

注:红色加粗是新增加的 TNAME, r.comments sys.all_tab_comments r,   and t.table_name = r.table_name,   and t.owner = r.owner

{OWNER, TABLE, TNAME}

select
   t.owner,
   t.table_name,
   r.comments
from
   sys.all_tab_comments r
   sys.all_all_tables t
where
   not exists (select 1 from sys.all_mviews s where s.owner = t.owner and t.table_name in (s.mview_name, s.update_log))
   and t.iot_name is null
   and nvl(t.dropped, 'NO') = 'NO'
[  and t.table_name=%.q:TABLE%]
[  and t.owner=%.q:SCHEMA%]
   and t.table_name = r.table_name
   and t.owner = r.owner
order by
   t.owner, t.table_name