PowerDesigner 16 sql server 2008生成备注报“对象名 'sysproperties' 无效”解决办法
从网上找了一段生成sql备注的脚本，脚本如下：
从网上找了一段生成sql备注的脚本，脚本如下：
[plain]?
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?
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里也好像没有)。
[sql]?
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?
?from?sysproperties?
?where?id?=?object_id('T_PRI_CarLimitPriceCache')?
?and?type?=?3)?
?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里生成的脚本如下：
[sql]?
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?
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脚本如下：