本文介绍了如何解决使用PowerDesigner 15进行数据库逆向工程时，无法获取列注释的问题。通过编辑DBMS Properties的SqlListQuery，添加对`MS_Description`的查询，并创建自定义DBMS配置，可以成功获取到列的注释。此外，还提供了通过vbs脚本来自动将列的Comment设置为Name的方案。
上一章讲了对数据库进行逆向工程生成图表操作，可能会遇到无法生成注释的问题：
一、解决PowerDesigner逆向工程没有列注释
1、打开PowerDesigner 15,选择菜单：File→Reverse Engineer→Database 对数据库进行逆向工程生成PDM图表，选择一张表生成：
生成后双击图表，打开表属性，选择Columns选项，可以看到注释却是空的，而数据库里的表是有注释的
2、选择菜单：Database→Edit Current DBMS，弹出DBMS Properties对话框，选择Script\Objects\Column\SqlListQuery
3、可以看到其Value为：
{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}selectu.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 ascolnA,
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 ascolnB,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 ascolnC,convert(varchar(8000), d.definition),case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ',' + convert(varchar, i.increment_value) else '' end ascolnD,
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column', c.name) where name = 'MS_Description') ascolnE,
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)whereo.typein ('U', 'S', 'V')[and u.name = %.q:OWNER%]
[and o.name=%.q:TABLE%]
order by 1, 2, 3
原脚本
将其修改一下就可以了。
{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc,COMMENT, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}selectu.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 ascolnA,
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 ascolnB,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 ascolnC,convert(varchar(8000), d.definition),case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ',' + convert(varchar, i.increment_value) else '' end ascolnD,convert(varchar(8000), e.value) ascolnE,
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)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')whereo.typein ('U', 'S', 'V')[and u.name = %.q:OWNER%]
[and o.name=%.q:TABLE%]
order by 1, 2, 3
新脚本
4、这里我为了以后还可以使用原先的脚本DBMS(也就是自带的Microsoft SQL Server 2008)，我新建一个自定义的“My Microsoft SQL Server 2008”：选择菜单，Tools→Resources→DBMS，弹出List Of DBMS对话框，选择新建，弹出新建对话框，填写名称为“My Microsoft SQL Server 2008”，选择“Microsoft SQL Server 2008”从此复制，Ok，保存。
5、保存后，弹出DBMS Properties对话框，选择Script\Objects\Column\SqlListQuery，修改其Value就可以了，Value的值就是上面的“新脚本”的值。
6、选择File→Reverse Engineer→Database 对数据库进行逆向工程，DBMS选择刚才新建的“My Microsoft SQL Server 2008”，这样生成的表就有注释了。
二、让列的Name自动设为为Comment的中文意思：
1、在刚才新建的“My Microsoft SQL Server 2008”的Script\Objects\Column中可以看到这么一段:
The following system variables are available:
(parenttable items are also available forcolumns)
"COLUMN" // generated code of the column"COLNNO"// position of the column in the list of columns of the table"COLNNAME"// name of the column"COLNCODE"// code of the column"PRIMARY" // keyword "primary" if the column is primary"ISPKEY"// TRUE if the column is part of the primary key"FOREIGN" // TRUE if the column is part of one foreign key
2、将COLNNAME添加到Script\Objects\Column\SqlListQuery的Value中，{OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc,COMMENT,COLNNAME, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream, ExtRowGuidCol}，再把Comment的值给它
现在生成效果就很好了：
3、也可以不用这种方式来完成Name自动设为Comment，可以利用vbs脚本完成
'******************************************************************************'* File: comment2name.vbs'* Purpose: 在PowerDesigner的PDM图形窗口中显示数据列的中文注释'* Title: 将字段的comment赋值到字段的name中'* Category: 打开物理模型，运行本脚本(Ctrl+Shift+X)'* Copyright:foxzz@163.com,2006/07/25 .'* Author: foxzz'* Created:'* Modified:'* Version: 1.0'* Comment: 遍历物理模型中的所有表，将字段的comment赋值到字段的name中。'在将name置换为comment过程中，需要考虑的问题'1、name必须唯一，而comment有可能不唯一。'处理办法是如果字段的comment重复，则字段的name=comment+1、2、3...'2、comment值有可能为空，这种情况下对字段的name不处理。'针对oracle数据库,将comment on column 字段名称 is '';添加到C:/pdcomment.txt文件中。'在补充comment完毕后，便于在数据库中执行'******************************************************************************
Option ExplicitValidationMode= TrueInteractiveMode=im_BatchDimsystem, fileSet system = CreateObject("Scripting.FileSystemObject")Dim ForReading, ForWriting, ForAppending '打开文件选项
ForReading = 1 '只读
ForWriting = 2 '可写
ForAppending = 8 '可写并追加'打开文本文件
Set file = system.OpenTextFile("C:/pdcomment.txt", ForWriting, true)'判断当前model是否物理数据模型
DimmdlSet mdl =ActiveModelIf (mdl Is Nothing) Then
MsgBox "处理对象无模型"
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
MsgBox "当前模型不是物理数据模型"
ElseProcessFolder mdl,fileEnd Iffile.Close'******************************************************************************
Private subProcessFolder(folder,file)Dimi,j,k
i=0:j=0:k=0
'列数组，记录字段里不重复的comment
DimColumnComment()DimColumnCommentNumber()ReDim PreserveColumnComment(i)ReDim PreserveColumnCommentNumber(i)Dim tbl '当前表
Dim col '当前字段
dim curComment '当前字段comment
'处理模型中的表
for each tbl infolder.tablesif not tbl.isShortcut then
if len(trim(tbl.comment))<>0 then
'可以在这里显示table的comment
'tbl.name = tbl.name+"("+trim(tbl.comment)+")"
end if
'处理表中的列
for each col intbl.columns
k= 0curComment= trim(col.comment)if len(curComment)<>0 then
'遍历相异的comment数组
for j = 0 toiif ColumnComment(j) = curComment then
'如果找到相同的comment,则相关计数器加1
ColumnCommentNumber(j) = ColumnCommentNumber(j) + 1k=jend if
Next
'如果没有相同的comment,则k=0,此时ColumnCommentNumber(0)也为0
'否则ColumnCommentNumber(k)不为0
if ColumnCommentNumber(k) <> 0 thencol.name= curComment & cstr(ColumnCommentNumber(k))elsecol.name=curComment'ColumnComment(0)、ColumnCommentNumber(0)永远为空
'将相异的comment记录添加到数组中
i = i + 1
ReDim PreserveColumnComment(i)ReDim PreserveColumnCommentNumber(i)
ColumnComment(i)=curComment
ColumnCommentNumber(i)= 0
end if
else
'写入文件中
file.WriteLine "comment on column"+ tbl.name+"."+col.code+"is '';"
end if
next
end if
'由于不同表的name允许相同,因此此时重新初始化。
'因为ColumnComment(0)、ColumnCommentNumber(0)为空，可以保留
ReDim Preserve ColumnComment(0)ReDim Preserve ColumnCommentNumber(0)
i=0:j=0:k=0
next
Dim view '当前视图
for each view infolder.Viewsif not view.isShortcut then
'可以在这里显示view的comment
'view.name = view.comment
end if
next
'对子目录进行递归
Dim subpackage 'folder
For Each subpackage Infolder.Packagesif not subpackage.IsShortcut thenProcessFolder subpackage , fileend if
Next
end sub
Comment2Name
选择菜单：Tools→Execute Commands→Edit/Run Script，Run运行vbs脚本即可。
4、将Name的值设给Comment
'把pd中那么name想自动添加到comment里面'如果comment为空,则填入name;如果不为空,则保留不变,这样可以避免已有的注释丢失.
Option ExplicitValidationMode= TrueInteractiveMode=im_BatchDim mdl 'the current model
'get the current active model
Set mdl =ActiveModelIf (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."
ElseProcessFolder mdlEnd If
'This routine copy name into comment for each table, each column and each view'of the current folder
Private subProcessFolder(folder)Dim Tab 'running table
for each Tab infolder.tablesif not tab.isShortcut then
if trim(tab.comment)="" then '如果有表的注释,则不改变它.如果没有表注释.则把name添加到注释里面.
tab.comment = tab.nameend if
Dim col 'running column
for each col in tab.columnsif trim(col.comment)="" then '如果col的comment为空,则填入name,如果已有注释,则不添加;这样可以避免已有注释丢失.
col.comment=col.nameend if
next
end if
next
Dim view 'running view
for each view infolder.Viewsif not view.isShortcut and trim(view.comment)="" thenview.comment=view.nameend if
next
'go into the sub-packages
Dim f 'running folder
For Each f Infolder.Packagesif not f.IsShortcut thenProcessFolder fend if
Next
end sub
Name2Comment
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
