在用powerdesigner逆向工程生成PDM时,列注释(ColumnComment)始终无法生成,历经数小时的探索,找到一个折衷的方法,现分享如下。并希望有高手指点更好的方法。
邀月使用的是Powerdesigner 15.2,数据库为SQL Server 2008 r2
方法如下:
1、在PowerDesigner界面-File-Reverse Engineer-Database,然后选择一个DBMS,我这里选取一个由系统默认的SQL Server 2008 DBMS修改而来的SQL_2008_MyDefine。
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/8100bcc14282a6222b675c41a075c3eb.png)
选好odbc,输入正确的连接串,选择数据库及表。
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/4857a18ee3a66bd90c583279090eac0f.png)
生成效果:Name为英文,且Comment列为空。
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/76561bac0b3ffa87f9b88493e3d224c6.png)
而我们希望的是Name列为中文,Comment为中文。
查看了下在线帮助,发现在SQL_2008_MyDefine::Script\Objects\Column\SqlListQuery下的Value值如下:
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/cd98c2e04714104117b2d75b57691bb2.png)
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 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
注意该值中上面是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 c
join
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,而不是用数据库就可以生成注释。如下图:
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/00e874fac7c6634109355c64556eea4b.png)
但字段类型全乱了。
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/3654f0c16c0e60c7c6a29d9e7238ab49.png)
难道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}
结果生成如下:
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/72406709df5a9eeaea160cd9377e1d59.png)
同理将SQL_2008_MyDefine::Script\Objects\Table\SqlListQuery下的Value的第一行中
{OWNER, TABLE, TABLE_TYPE, Comment}的Comment改为{OWNER, TABLE, TABLE_TYPE, TNAME}
生成结果,如下:
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/031b4ae5c053bb7027fcf82f0947432e.png)
至此,我的基本目的,已经达到了,虽然没有直接生成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,
如下图:
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/2e020428863c3e8a59333e8d396a7ba4.png)
这样,Name列和Comment同时生成,终于OK!
![邀月工作室 邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/e51f034567b5ef5a0244bf97582d1b79.png)