在用powerdesigner逆向工程生成PDM時(shí)，列注釋(ColumnComment)始終無(wú)法生成，歷經(jīng)數(shù)小時(shí)的探索，找到一個(gè)折衷的方法，現(xiàn)分享如下。并希望有高手指點(diǎn)更好的方法。

邀月使用的是Powerdesigner 15.2，數(shù)據(jù)庫(kù)為SQL Server 2008 r2

方法如下：

1、在PowerDesigner界面－File-Reverse Engineer－Database,然后選擇一個(gè)DBMS,我這里選取一個(gè)由系統(tǒng)默認(rèn)的SQL Server 2008 DBMS修改而來(lái)的SQL_2008_MyDefine。

選好odbc，輸入正確的連接串，選擇數(shù)據(jù)庫(kù)及表。

生成效果：Name為英文，且Comment列為空。

而我們希望的是Name列為中文，Comment為中文。

查看了下在線幫助，發(fā)現(xiàn)在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對(duì)應(yīng)的列名，下面是從SQL數(shù)據(jù)庫(kù)中取到的相關(guān)屬性值。其中“

(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語(yǔ)句，將語(yǔ)句提取出來(lái)，也可以獲取結(jié)果：

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

后來(lái)發(fā)現(xiàn)，直接用生成表的SQL，而不是用數(shù)據(jù)庫(kù)就可以生成注釋。如下圖：

但字段類型全亂了。

難道Powerdesigner真的這么弱嗎？google了一下，看到官方文檔：

http://manuals.sybase.com/onlinebooks/group-pd/pdd1100e/advanced/@Generic__BookTextView/1302;hf=0

里面有關(guān)于SqlListQuery的解釋。

再看看上面的SQL語(yǔ)句，突然想到，既然可以在SQL查詢出結(jié)果，那么出錯(cuò)一定在生成PDM的時(shí)候，

在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”會(huì)如何？即

{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}

結(jié)果生成如下：

同理將SQL_2008_MyDefine::Script/Objects/Table/SqlListQuery下的Value的第一行中

{OWNER, TABLE, TABLE_TYPE, Comment }的Comment改為{OWNER, TABLE, TABLE_TYPE, TNAME }

生成結(jié)果，如下：

至此，我的基本目的，已經(jīng)達(dá)到了，雖然沒(méi)有直接生成Comment列，但可以通過(guò)以下vbs腳本，從Name列生成Comment列：

'代碼一:將name生成comment的腳本
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

仔細(xì)思索：為什么從SQL生成的腳本有Comment,可是到生成PDM時(shí)為什么沒(méi)有轉(zhuǎn)化成功呢？是否Comment名稱不對(duì)應(yīng)？

于是將 原來(lái)的 COMMENT 加上 COLNNAME ，同時(shí)在下面的SQL語(yǔ)句中增加一列與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同時(shí)生成，終于OK！

powerdesigner逆向工程生成PDM時(shí)的列注釋解決方案