PowerDesigner16导出Sql修复

最近在对公司的系统做重构，用了pd16,导出到sqlserver2008时，出了一些问题，以前碰到过解决了，但再次碰到时，还是要查阅资料，所以这次把解决办法分享出来。

一，在数据库中创建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%
]