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

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

第一步:在Sql 2005/2008 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)

 第二 步: 修改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%

]