在用powerdesigner逆向工程生成PDM时,列注释(ColumnComment)始终无法生成,历经数小时的探索,找到一个折衷的方法,现分享如下。并希望有高手指点更好的方法。
邀月使用的是Powerdesigner 15.2,数据库为SQL Server 2008 r2
方法如下:
1、在PowerDesigner界面-File-Reverse Engineer-Database,然后选择一个DBMS,我这里选取一个由系统默认的SQL Server 2008 DBMS修改而来的SQL_2008_MyDefine。
选好odbc,输入正确的连接串,选择数据库及表。
生成效果:Name为英文,且Comment列为空。
而我们希望的是Name列为中文,Comment为中文。
查看了下在线帮助,发现在SQL_2008_MyDefine::Script\Objects\Column\SqlListQuery下的Value值如下:
View Code
{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 cjoin [%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
注意该值中上面是PDM对应的列名,下面是从SQL数据库中取到的相关属性值。其中“
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column', c.name) where name = 'MS_Description') as colnE,
”是可以取到Comment值的,可是生成的PDM为什么Comment列为空呢?尝试修改该SQL语句,将语句提取出来,也可以获取结果:
View Code
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 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 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 sys.columns cjoin sys.objects o on (o.object_id = c.object_id)join sys.schemas u on (u.schema_id = o.schema_id)join sys.types t on (t.user_type_id = c.system_type_id)left outer join sys.identity_columns i on (i.object_id = c.object_id and i.column_id = c.column_id)left outer join sys.default_constraints d on (d.object_id = c.default_object_id)where o.type in ('U', 'S', 'V')and u.name =N'dbo' and o.name=N'Age' order by 1, 2, 3
后来发现,直接用生成表的SQL,而不是用数据库就可以生成注释。如下图:
但字段类型全乱了。
难道Powerdesigner真的这么弱吗?google了一下,看到官方文档:
http://manuals.sybase.com/onlinebooks/group-pd/pdd1100e/advanced/@Generic__BookTextView/1302;hf=0
里面有关于SqlListQuery的解释。
再看看上面的SQL语句,突然想到,既然可以在SQL查询出结果,那么出错一定在生成PDM的时候,
在SQL_2008_MyDefine::Script\Objects\Column中看到这么一段:
The following system variables are available:
(parent table items are also available for columns)
"COLNNAME" // name of the column "COLNCODE" // code of the column
将上面的Value值中上面的一行中“Comment”修改为“COLNNAME”会如何?即
{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}改为
{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COLNNAME, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}
结果生成如下:
同理将SQL_2008_MyDefine::Script\Objects\Table\SqlListQuery下的Value的第一行中
{OWNER, TABLE, TABLE_TYPE, Comment}的Comment改为{OWNER, TABLE, TABLE_TYPE, TNAME}
生成结果,如下:
至此,我的基本目的,已经达到了,虽然没有直接生成Comment列,但可以通过以下vbs脚本,从Name列生成Comment列:
'代码一:将name生成comment的脚本
View Code
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 'This routine copy name into code for each table, each column and each view 'of the current folder Private sub ProcessFolder(folder) Dim Tab 'running table for each Tab in folder.tables if not tab.isShortcut then
tab.comment = tab.name Dim col 'running column for each col in tab.columns
col.comment= col.name next end if next Dim view 'running view for each view in folder.Views if not view.isShortcut then
view.comment = view.name 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
仔细思索:为什么从SQL生成的脚本有Comment,可是到生成PDM时为什么没有转化成功呢?是否Comment名称不对应?
于是将原来的COMMENT加上COLNNAME,同时在下面的SQL语句中增加一列与COMMENT相同的值:
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column', c.name) where name = 'MS_Description') as colnF,
如下图:
这样,Name列和Comment同时生成,终于OK!
相关推荐
Powerdesigner逆向工程生成PDM 中文列名表名替换为注释
PowerDesigner逆向工程生成PDM模型及数据库
powerdesigner逆向工程导入mysql脚本生成PDM带全注释
PowerDesigner逆向工程导入MYSQL数据库总结 PowerDesigner逆向工程导入MYSQL数据库总结
使用Powerdesigner的逆向工程生成PDM.pdf
PowerDesigner逆向工程-Mysql,并解决没有注释的问题!
在数据库设计中,经常使用数据库逆向工程,将数据库中库表结构转为PDM,为了阅读方便,需要将将表字段的comment转换为PDM的name。该文件解决此问题
POWERDESIGNER逆向工程从现有数据库生成PDM
Powerdesigner逆向工程Mysql导出表pdm Powerdesigner逆向工程Mysql导出表pdm Powerdesigner逆向工程Mysql导出表pdm
使用PowerDeSigner逆向生成Mysql数据库PDM图(带中文注释)-附件资源
PowerDesigner16.5逆向工程显示字段备注,生成的更贴合。使用的是命令运行的方式,本人亲试,非常方便。
通常数据表字段使用英文,备注comment完成字段的说明。使用该脚本能够在PowerDesigner批量生成SQL脚本时将name生成数据库中字段的comment
PowerDesigner逆向工程 PowerDesigner逆向工程 PowerDesigner逆向工程
Powerdesigner逆向工程从现有数据库生成PDM.doc
在数据建模过程中,我们建立概念数据模型,通过正向工程生成物理数据模型,生成数据库建库脚本,最后将物理数据模型生成关系数据库,现在反过来,通过逆向工程将关系数据库,生成物理数据模型。
PowerDesigner导出的SQL带列注释,导入到MySQL中列注释不见了的处理方法
1 PowerDesigner逆向工程 传说中,程序员们喜欢用powerDesign进行数据库建模。通常都是先设计出物理模型图,在转换出数据库需要的SQL语句,从而生成数据库。 但,江湖中流传着“powerDesign逆向工程”的传说。好,...
在PowerDesigner里执行脚本命令,从Excel里读取表结构生成pdm文件。
如何用PowerDesigner逆向工程导出ORACLE数据库表
当PowerDesigner连接好数据库,并生成模型后,在PowerDesigner用Ctrl+Shift+x快捷键打开,然后将脚本内容粘贴运行,就可以开始生成数据库中存在的注释了