解决PowerDesigner 反向工程没有列注释(SQLSERVER 2005版)

 解决方案：     

在PD的 Tools-->Resources-->DBMS-->SQL Server 2005打开DBMS属性窗口，在General选项卡中选择Microsoft SQL Server 2005-->Script-->Objects-->Column-->SQLlistquery修改其中的内容为:

 

 

 
    
   {OWNER,  
   TABLE 
   , S,  
   COLUMN 
   , DTTPCODE, LENGTH, SIZE, PREC,  
   COMPUTE 
   , NOTNULL,  
   IDENTITY 
   , DOMAIN,  
   DEFAULT 
   , ExtIdentitySeedInc, COMMENT, ExtCollation, ExtIdtNotForReplication, ExtDeftConstName, 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,
     
   convert 
   ( 
   varchar 
   ( 
   8000 
   ),e.value)  
   as 
    colnE,
    c.collation_name,
     
   case 
    (i.is_not_for_replication)  
   when 
     
   1 
     
   then 
     
   ' 
   true 
   ' 
     
   else 
     
   ' 
   false 
   ' 
     
   end 
   ,
    d.name,
     
   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)
     
   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 
   ' 
   )
 
   where 
    
   o.type  
   in 
    ( 
   ' 
   U 
   ' 
   ,  
   ' 
   S 
   ' 
   ,  
   ' 
   V 
   ' 
   )
 
   [ 
     and u.name = %.q:OWNER% 
   ] 
   
 
   [ 
     and o.name=%.q:TABLE% 
   ] 
   
 
   order 
     
   by 
     
   1 
   ,  
   2 
   ,  
   3 
  

 

 

其实只是更改了

convert(varchar(8000),e.value) as colnE,

 

新加了

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')

本文参考:

wolfy的<<解决PowerDesigner 反向工程没有列注释(SQLSERVER 2008版) >>

http://www.cnblogs.com/wolfly/archive/2009/04/25/1443561.html