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脚本如下：