本文介绍了一种方法，通过修改PowerDesignern的SQL查询脚本，实现从SQL数据库导入中文注释到PDM字段。包括具体操作步骤、SQL语句修改以及最终的解决方法，同时分享了通过VBS脚本辅助生成Comment列的技巧。
转至：http://www.cnblogs.com/downmoon/archive/2011/03/04/1971250.html。
该方法可以使得数据表的注释 导入到pdm的字段的name里面，相当方便。
在用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值如下：
{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语句，将语句提取出来，也可以获取结果：
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
仔细思索：为什么从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！
扫一扫
272
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
