首页 > 代码库 > 解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题

解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题

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

再生成数据库SQL脚本执行,共分为三部:

第一步:在Sql 2005/2008 Exec the Sql 创建View ‘sysproperties‘

if exists (select 1 from sysobjects where name = syspropertiesand xtype = V)begin    DROP VIEW syspropertiesend    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 1from syspropertieswhere 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 sysnameselect @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/zhouchaoyi/archive/2012/03/07/2383509.html

解决PowerDesigner 16 Generate Datebase For Sql2005/2008 对象名sysproperties无效的问题