解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
来源:互联网 发布:上海绿茶网络vip 编辑:程序博客网 时间:2024/05/31 19:22
造成此问题的原因是由于Sql 2005、2008 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致 , 以下是通过创建sysproperties视图,以及修改powerdesigner sql语句生成模板后,再生成数据库SQL脚本执行
-------------------------------------------------
Sql 2005 Exec the 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)
--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 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 的错误
仅解决对象名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
][%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%
]
-------------------------------------------------
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%
]
转自:http://hi.baidu.com/terry_zi/blog/item/67659ba149935ae89152ee60.html
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005 找不到sysproperties表的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005 找不到sysproperties表的问题
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysprop
- PD16 Generate Datebase For Sql2008R2时报脚本错误“对象名sysproperties无效”
- 解决方案:PowerDesigner 16生成的SqlServer 2000/2005/2008脚本执行出现:“对象名sysproperties无效”
- 解决PowerDesigner 生成Sql2005-2012 找不到sysproperties表的问题
- PowerDesigner 16 sql server 2008 生成备注报“对象名 'sysproperties' 无效”解决办法。
- PowerDesigner 16 sql server 2008 生成脚本时备注报“对象名 'sysproperties' 无效”解决办法
- 对象名 'sysproperties' 无效
- SQL2005中 sysproperties 无效
- SQL2000的系统表sysproperties在SQL2005中 无效的 问题
- SQL2000的系统表sysproperties在SQL2005中 无效的问题
- 正确的Linux菱形乱码修改方法
- <<<I、O概述及字符流基本操作>>>
- 数学之美系列十:有限状态机和地址识别
- Activity之屏幕方向
- 数据库范式
- 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题
- sql语句将excel数据导入到表
- 《HT06,Tagging Papper, Taxonomy,Flickr,Academic Article,ToRead》
- PL/SQL 集合的初始化与赋值
- 数学之美系列十一:Google 阿卡 47 的制造者阿米特.辛格博士
- 数学之美系列十二:余弦定理和新闻的分类
- UltraEdit v18及注册
- 警惕程序优化的七大陷阱
- Linux -- U盘的挂载和卸载