在用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 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，而不是用数据库就可以生成注释。如下图：
但字段类型全乱了。
难道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！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
