【转】解决PowerDesigner 反向工程没有注释（备注）(SQL Server2008)+ Sql导入powerdesigner时中文丢失问题（Oracle11g）

一、SQL Server2008

解决PowerDesigner 反向工程没有注释（备注）(SQL Server2008)部分 转载自：http://www.cnblogs.com/zhangxb/archive/2012/04/20/2458898.html

1. 列注释

原来代码：

{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}

select
u.name,
o.name,
c.column_id,
c.name,
case when c.system_type_id in (165, 167, 231) and c.max_length = -1 then t.name + '(Max)' else t.name end,
c.precision,
case (c.max_length) when -1 then 0 else case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else (c.max_length) end end as colnA,
c.scale,
case(c.is_computed) when 1 then convert(varchar(8000), (select z.definition from [%CATALOG%.]sys.computed_columns z where z.object_id = c.object_id and z.column_id = c.column_id)) else '' end as colnB,
case(c.is_nullable) when 1 then 'NULL' else 'NOTNULL' end,
case(c.is_identity) when 1 then 'identity' else '' end,
case when(c.user_type_id <> c.system_type_id) then (select d.name from [%CATALOG%.]sys.types d where d.user_type_id = c.user_type_id) else '' end as colnC,
convert(varchar(8000), d.definition),
case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ', ' + convert(varchar, i.increment_value) else '' end as colnD,
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column', c.name) where name = 'MS_Description') as colnE,
c.collation_name,
case (i.is_not_for_replication) when 1 then 'true' else 'false' end,
d.name,
case(c.is_sparse) when 1 then 'true' else 'false' end,
case(c.is_filestream) when 1 then 'true' else 'false' end,
case(c.is_rowguidcol) when 1 then 'true' else 'false' end
from
[%CATALOG%.]sys.columns c
join [%CATALOG%.]sys.objects o on (o.object_id = c.object_id)
join [%CATALOG%.]sys.schemas u on (u.schema_id = o.schema_id)
join [%CATALOG%.]sys.types t on (t.user_type_id = c.system_type_id)
left outer join [%CATALOG%.]sys.identity_columns i on (i.object_id = c.object_id and i.column_id = c.column_id)
left outer join [%CATALOG%.]sys.default_constraints d on (d.object_id = c.default_object_id)

where
o.type in ('U', 'S', 'V')
[ and u.name = %.q:OWNER%]
[ and o.name=%.q:TABLE%]
order by 1, 2, 3

 

解决方案：

在PD的 tools-->resources-->dbms-->sql server 2008打开DBMS属性窗口，在general选项卡中选择Microsoft SQL Server 2008-->script-->objects-->column-->SQLlistquery修改其中的内容为:

{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}

select
u.name,
o.name,
c.column_id,
c.name,
case when c.system_type_id in (165, 167, 231) and c.max_length = -1 then t.name + '(Max)' else t.name end,
c.precision,
case (c.max_length) when -1 then 0 else case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else (c.max_length) end end as colnA,
c.scale,
case(c.is_computed) when 1 then convert(varchar(8000), (select z.definition from [%CATALOG%.]sys.computed_columns z where z.object_id = c.object_id and z.column_id = c.column_id)) else '' end as colnB,
case(c.is_nullable) when 1 then 'NULL' else 'NOTNULL' end,
case(c.is_identity) when 1 then 'identity' else '' end,
case when(c.user_type_id <> c.system_type_id) then (select d.name from [%CATALOG%.]sys.types d where d.user_type_id = c.user_type_id) else '' end as colnC,
convert(varchar(8000), d.definition),
case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ', ' + convert(varchar, i.increment_value) else '' end as colnD,
convert(varchar(8000),e.value),
c.collation_name,
case (i.is_not_for_replication) when 1 then 'true' else 'false' end,
d.name,
case(c.is_sparse) when 1 then 'true' else 'false' end,
case(c.is_filestream) when 1 then 'true' else 'false' end,
case(c.is_rowguidcol) when 1 then 'true' else 'false' end
from
[%CATALOG%.]sys.columns c
join [%CATALOG%.]sys.objects o on (o.object_id = c.object_id)
join [%CATALOG%.]sys.schemas u on (u.schema_id = o.schema_id)
join [%CATALOG%.]sys.types t on (t.user_type_id = c.system_type_id)
left outer join [%CATALOG%.]sys.identity_columns i on (i.object_id = c.object_id and i.column_id = c.column_id)
left outer join [%CATALOG%.]sys.default_constraints d on (d.object_id = c.default_object_id)
left outer join [%CATALOG%.]sys.extended_properties e on (e.class=u.schema_id and e.major_id=o.object_id and e.minor_id = c.column_id and e.name=N'MS_Description')
where
o.type in ('U', 'S', 'V')
[ and u.name = %.q:OWNER%]
[ and o.name=%.q:TABLE%]
order by 1, 2, 3

 

 

2. 表注释

原为：

{OWNER, TABLE, TABLE_TYPE, COMMENT}

select
u.name,
o.name,
case (o.type) when 'S' then 'SYSTEM TABLE' else 'TABLE' end,
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, null, null) where name = 'MS_Description') as coln
from
[%CATALOG%.]sys.sysobjects o
join [%CATALOG%.]sys.schemas u on (u.schema_id = o.uid)
where
o.type in ('U', 'S')
[ and u.name = %.q:OWNER%]
order by 1, 2

解决办法：

在PD的 tools-->resources-->dbms-->sql server 2008打开DBMS属性窗口，在general选项卡中选择Microsoft SQL Server 2008-->script-->objects-->Table-->SQLlistquery修改其中的内容为:

{OWNER, TABLE, TABLE_TYPE, COMMENT}

select
u.name,
o.name,
case (o.type) when 'S' then 'SYSTEM TABLE' else 'TABLE' end
,convert(varchar(8000), e.value) as coln
from
[%CATALOG%.]sys.sysobjects o
join [%CATALOG%.]sys.schemas u on (u.schema_id = o.uid)
left join [%CATALOG%.]sys.extended_properties e on (e.class=u.schema_id and e.major_id=o.id and e.minor_id=0 and e.name=N'MS_Description')
where
o.type in ('U', 'S')
[ and u.name = %.q:OWNER%]
order by 1, 2

3. 反向工程后，在Users中删除dbo用户，然后生成脚本即可运行。

 

二、Sql导入powerdesigner时中文丢失问题（Oracle11g）

（原文是针对Oracle11g,SqlServer2008未测试通过） 

当将sql导入powerdesigner时，中文注释丢失在表结构中全部丢失， 

这时只需运行如下vb脚本即可解决此问题：快试试吧！

PowerDesigner->Tools->Execute Commands->Edit/Run Scripts 

运行脚本如下：

 

Option   Explicit     

ValidationMode   =   True     

InteractiveMode   =   im_Batch    

   

Dim   mdl   '   the   current   model    

   

'   get   the   current   active   model     

Set   mdl   =   ActiveModel     

If   (mdl   Is   Nothing)   Then     

       MsgBox   "There   is   no   current   Model "      

ElseIf   Not   mdl.IsKindOf(PdPDM.cls_Model)   Then     

       MsgBox   "The   current   model   is   not   an   Physical   Data   model. "      

Else     

      ProcessFolder   mdl     

End   If    

   

Private   sub   ProcessFolder(folder)     

On Error Resume Next    

      Dim   Tab   'running     table     

       for   each   Tab   in   folder.tables      

             if   not   tab.isShortcut   then      

                  tab.name   =   tab.comment    

                  Dim   col   '   running   column     

                   for   each   col   in   tab.columns      

                   if col.comment="" then     

                   else    

                        col.name=   col.comment     

                   end if    

                  next     

             end   if      

      next    

   

      Dim   view   'running   view     

       for   each   view   in   folder.Views      

             if   not   view.isShortcut   then      

                  view.name   =   view.comment     

             end   if      

      next    

   

      '   go   into   the   sub-packages     

      Dim   f   '   running   folder     

      For   Each   f   In   folder.Packages     

             if   not   f.IsShortcut   then      

                 ProcessFolder   f     

             end   if      

      Next     

end   sub