PowerDesigner 16 sql server 2008 生成备注报“对象名 'sysproperties' 无效”解决办法。

从网上找了一段生成sql备注的脚本，脚本如下：

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   comment   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

上面这段脚在 PowerDesigner 15上没有问题，可是到了PowerDesinger 16上就出现在问题了。

在PowerDesinger 16上生成出来的脚本里有多了东西，就是加了一个判断，如果备注已经存在，就删除这个判断，在对其进行判断的时候，用到了sysproperties这表表，可是在sql server 2008 里面没有(sql server 2005里也好像没有)。

if exists (select 1 
            from  sysproperties 
           where  id = object_id('T_PRI_CarLimitPriceCache') 
            and   type = 3) 
begin 
   declare @CurrentUser sysname 
select @CurrentUser = user_name() 
execute sp_dropextendedproperty 'MS_Description',  
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache' 
 
end 

select @CurrentUser = user_name() 
execute sp_addextendedproperty 'MS_Description',  
   '限价设定数据缓存', 
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache'
go

if exists (select 1
            from  sys.properties
           where  id = object_id('T_PRI_CarLimitPriceCache')
            and   type = 4)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache', 'column', 'MarketCode'


end

那PowerDesinger 15里生成的脚本如下：

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sys.sp_addextendedproperty 'MS_Description', 
   '限价设定数据缓存',
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '店面编码',
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache', 'column', 'MarketCode'
go

为什么会这样呢？最后查看了 PowerDesinger 16 与 PowerDesinger 15里面的 TableComment 和 ColumnComment 里内脚本不一样导至生成的出来的脚本不同。

在网上找了一下发生sql server 2008 数据库里没有 sysproperties这个表，所在执行生成 PowerDesigner 16生成出来的的脚本出现下图所错误

现在有二个方法可以解决这个问题，那就是把 PowerDesigner 15里面TableComment和ColumnComment的脚本放到 PowerDesigner 16里面生成出来看脚本就像15一样，在执行的时候也就出报上面的错误了。

第二个方法就是修改PowerDesigner 16里面的脚本内容。

表：

列：

修改完了之后，生成的SQL脚本如下：

if exists (select 1 
            from  sys.extended_properties
           where  major_id = object_id('T_PRI_CarLimitPriceCache') 
            and   minor_id = 0) 
begin 
   declare @CurrentUser sysname 
select @CurrentUser = user_name() 
execute sp_dropextendedproperty 'MS_Description',  
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache' 
 
end 

select @CurrentUser = user_name() 
execute sp_addextendedproperty 'MS_Description',  
   '限价设定数据缓存', 
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache'
go

if exists (select 1
            from  sys.extended_properties
           where  major_id= object_id('T_PRI_CarLimitPriceCache')
            and   value = '店面编码')
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'T_PRI_CarLimitPriceCache', 'column', 'MarketCode'

end

再执行的时候就不会报  [对象名 'sysproperties' 无效。]这个错误了。

现在把PowerDesigner 15里的TableComment 和 ColumnComment里的内容贴出来。

-------------------------TableComment-----------------------------------
[%OWNER%?[.O:[execute ][exec ]][sys.]sp_addextendedproperty [%R%?[@%S%=][N]]'MS_Description', 
   [%R%?[@%S%=][N]]%.q:COMMENT%,
   [%R%?[@%S%=][N]]'[.O:[user][schema]]', [%R%?[@%S%=][N]]%.q:OWNER%, [%R%?[@%S%=][N]]'table', [%R%?[@%S%=][N]]%.q:TABLE%
:declare @CurrentUser sysname
select @CurrentUser = user_name()
[.O:[execute ][exec ]][sys.]sp_addextendedproperty [%R%?[@%S%=][N]]'MS_Description', 
   [%R%?[@%S%=][N]]%.q:COMMENT%,
   [%R%?[@%S%=][N]]'user', [%R%?[@%S%=][N]]@CurrentUser, [%R%?[@%S%=][N]]'table', [%R%?[@%S%=][N]]%.q:TABLE%
]


-------------------------ColumnComment----------------------------------

[%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', 
   [%R%?[N]]%.q:COMMENT%,
   [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
:declare @CurrentUser sysname
select @CurrentUser = user_name()
[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', 
   [%R%?[N]]%.q:COMMENT%,
   [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
]

修改后的PowerDesigner 16里TableComment 和 ColumnComment里的内容贴出来。

---------------------------------TableComment------------------------------
[if exists (select 1 
            from  sys.extended_properties
           where  major_id = object_id('[%QUALIFIER%]%TABLE%') 
            and   minor_id = 0) 
begin 
   [%OWNER%?[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description',  
   [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE% 
:declare @CurrentUser sysname 
select @CurrentUser = user_name() 
[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description',  
   [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE% 
] 
end 


][%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description',  
   [%R%?[N]]%.q:COMMENT%, 
   [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE% 
:select @CurrentUser = user_name() 
[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description',  
   [%R%?[N]]%.q:COMMENT%, 
   [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE% 
] 
---------------------------------ColumnComment-----------------------------
[if exists (select 1
            from  sys.extended_properties
           where  major_id= object_id('[%QUALIFIER%]%TABLE%')
            and   value = [%R%?[N]]%.q:COMMENT%)
begin
   [%OWNER%?[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description', 
   [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
:declare @CurrentUser sysname
select @CurrentUser = user_name()
[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description', 
   [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
]

end


][%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', 
   [%R%?[N]]%.q:COMMENT%,
   [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
:select @CurrentUser = user_name()
[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', 
   [%R%?[N]]%.q:COMMENT%,
   [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
]