解决PowerDesigner 16 Generate Datebase For Sql2005 找不到sysproperties表的问题（转，并修改了里面的错误）

转自博客：

http://blog.csdn.net/xiaoqijun/article/details/6914209

造成此问题的原因是由于Sql 2005 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致，微软的目的不再去猜测

网上有二种解决方式但不符合本人的需要以下是通过创建sysproperties视图，以及改造powerdesigner sql语句生成模板实现本人直接用powerdesigner生成创建数据库SQL

方法实现简单，在此做个记录

-------------------------------------------------
Sql 2005 Exec the Sql 创建View 'sysproperties'
-------------------------------------------------

[sql] view plain copy print ?

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)
--WHERE A.name = 'T_WebUser'
GO

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)
--WHERE A.name = 'T_WebUser'
GO

-------------------------------------------------
Modified Table TableComment

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

-------------------------------------------------
Modified Column ColComment

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

[if exists (select 1
             from sys.extended_properties t,sys.columns c
            where t.major_id=c.object_id and t.major_id = object_id('[%QUALIFIER%]%TABLE%')   AND t.minor_id=c.column_id
             and   c.name = %.q:COLUMN% AND t.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%, [%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下执行不在报找不到sysproperties 的错误

仅解决找不到sysproperties 的错误其它错误欢迎讨论

以下是源模板备份

-------------------------------------------------
Old Table TableComment Bak
-------------------------------------------------
[if exists (select 1
from sysproperties
where id = object_id('[%QUALIFIER%]%TABLE%')
and type = 3)
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

-------------------------------------------------
Old Column ColComment Bak
-------------------------------------------------
[if exists (select 1
from sysproperties
where id = object_id('[%QUALIFIER%]%TABLE%')
and type = 4)
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%
]

posted on 2013-02-17 22:55 笨笨丁阅读(360) 评论(0) 编辑收藏举报

刷新页面返回顶部

善待自己,珍惜今天,恩泽他人,享受生活