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