powerdesigner逆向工程生成PDM时的列注释解决方案

powerdesigner逆向工程生成PDM时的列注释解决方案
2011年03月14日
　　在用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\SqlListQu ery下的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  
　　{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   
　　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/pdd 1100e/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\SqlListQue ry下的Value的第一行中
　　{OWNER, TABLE, TABLE_TYPE, Comment }的Comment改为{OWNER, TABLE, TABLE_TYPE, TNAME }
　　生成结果，如下：
　　
　　至此，我的基本目的，已经达到了，虽然没有直接生成Comment列，但可以通过以下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           
　　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！
　　
　　from http://blog.csdn.net/downmoon/archive/2011/03/04/6 224597.aspx