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%
]
