解决方案：PowerDesigner16生成的SqlServer2000/2005/2008脚本

2020-11-09 来源：欧得旅游网

现象：在PowerDesigner 16 中生成的sql语句,在执行的时候报错: 对象名sysproperties 无效的错误 ; 原因分析：造成此问题的原因是由于Sql 2005、2008 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致，以下是通过创建syspropert

现象：

在PowerDesigner 16 中生成的sql语句,在执行的时候报错:对象名sysproperties 无效的错误;

原因分析：

造成此问题的原因是由于Sql 2005、2008 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致，以下是通过创建sysproperties视图，以及修改powerdesigner sql语句生成模板后，再生成数据库SQL脚本执行,共分为三部:

第一步:

在Sql 2005/2008/2012查询分析器中执行下面的 Sql 语句创建View 'sysproperties'

if exists (select 1 from sysobjects where name = 'sysproperties'and xtype = 'V')
begin
 DROP VIEW sysproperties
end
 GO
 CREATE VIEW sysproperties
 AS
 SELECT A.name As TableName,
 A.id As TableID,B.Name As ColName,B.colid As ColID,
 B.xtype As ColType,C.name As PropName,C.Value As PropValue
 FROM sysobjects As A INNER JOIN syscolumns As B ON A.id = B.id
 INNER JOIN sys.extended_properties As C ON C.major_id = A.id 
 AND ( minor_id = B.colid)

第二步:

修改Table TableComment模板路径是 Database -> Edit Current DBMS 窗体 General 选项卡下 Script -> Objects -> Table -> TableComment

[if exists (select 1 
from sys.extended_properties 
where major_id = object_id('[%QUALIFIER%]%TABLE%') 
and minor_id = 0 and name = 'MS_Description') 
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% 
]

第三步:

修改Column ColumnComment模板路径是 Database -> Edit Current DBMS 窗体 General 选项卡下 Script -> Objects -> Column -> ColumnComment

[if exists (select 1
from sysproperties
where TableID = object_id('[%QUALIFIER%]%TABLE%')
and ColName = %.q:COLUMN% AND PropName='MS_Description')
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%
]

修改之后使用Generate Database 生成的SQL便可在SQL 2005/2008下执行不在报找不到sysproperties 的错误。

大功告成！！！

作者：记忆逝去的青春出处：http://www.cnblogs.com/lukun/http://www.cnblogs.com/lukun/ 联系我，非常感谢。

显示全文

全部栏目

解决方案：PowerDesigner16生成的SqlServer2000/2005/2008脚本