解决方案：PowerDesigner 16生成的SqlServer 2000/2005/2008脚本

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

现象：

 

在PowerDesigner 16 中生成的sql语句,在执行的时候报错:对象名sysproperties 无效的错误;

 

原因分析：

 

造成此问题的原因是由于Sql 2005、2008 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致 ， 以下是通过创建sysproperties视图，以及修改powerdesigner sql语句生成模板后，再生成数据库SQL脚本执行,共分为三部:

 

第一步:

 

在Sql 2005/2008/2012查询分析器中执行下面的 Sql 语句创建View 'sysproperties'

<span>if</span> <span>exists</span> (<span>select</span> <span>1</span> <span>from</span> sysobjects <span>where</span> name <span>=</span> <span>'</span><span>sysproperties</span><span>'</span><span>and</span> xtype <span>=</span> <span>'</span><span>V</span><span>'</span><span>)
</span><span>begin</span>
    <span>DROP</span> <span>VIEW</span><span> sysproperties
</span><span>end</span>
    <span>GO</span>
    <span>CREATE</span> <span>VIEW</span><span> sysproperties
    </span><span>AS</span>
    <span>SELECT</span> A.name <span>As</span><span> TableName,
    A.id </span><span>As</span> TableID,B.Name <span>As</span> ColName,B.colid <span>As</span><span> ColID,
    B.xtype </span><span>As</span> ColType,C.name <span>As</span> PropName,C.Value <span>As</span><span> PropValue
    </span><span>FROM</span> sysobjects <span>As</span> A <span>INNER</span> <span>JOIN</span> syscolumns <span>As</span> B <span>ON</span> A.id <span>=</span><span> B.id
    </span><span>INNER</span> <span>JOIN</span> sys.extended_properties <span>As</span> C <span>ON</span> C.major_id <span>=</span><span> A.id 
    </span><span>AND</span> ( minor_id <span>=</span> B.colid)

 

第二 步:

 

修改Table TableComment模板 路径是 Database -> Edit Current DBMS 窗体 General 选项卡 下 Script -> Objects -> Table -> TableComment

<span>[</span><span>if exists (select 1 
from sys.extended_properties 
where major_id = object_id('[%QUALIFIER%</span><span>]</span><span>%</span><span>TABLE</span><span>%</span><span>'</span><span>) 
and minor_id = 0 and name = </span><span>'</span>MS_Description<span>'</span><span>) 
begin 
[%OWNER%?[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>, 
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]%.q:OWNER%, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE% 
:declare @CurrentUser sysname 
select @CurrentUser = user_name() 
[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>, 
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]@CurrentUser, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE% 
] 
end 

][%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>, 
[%R%?[N]]%.q:COMMENT%, 
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]%.q:OWNER%, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE% 
:select @CurrentUser = user_name() 
[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>, 
[%R%?[N]]%.q:COMMENT%, 
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]@CurrentUser, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE% 
] </span>

 

第三步:

修改Column ColumnComment模板 路径是 Database -> Edit Current DBMS 窗体 General 选项卡 下 Script -> Objects -> Column -> ColumnComment

<span>[</span><span>if exists (select 1
from sysproperties
where TableID = object_id('[%QUALIFIER%</span><span>]</span><span>%</span><span>TABLE</span><span>%</span><span>'</span><span>)
and ColName = %.q:COLUMN% AND PropName=</span><span>'</span>MS_Description<span>'</span><span>)
begin
[%OWNER%?[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>,
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]%.q:OWNER%, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE%, [%R%?[N]]</span><span>'</span><span>column</span><span>'</span><span>, [%R%?[N]]%.q:COLUMN%
:declare @CurrentUser sysname
select @CurrentUser = user_name()
[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>, 
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]@CurrentUser, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE%, [%R%?[N]]</span><span>'</span><span>column</span><span>'</span><span>, [%R%?[N]]%.q:COLUMN%
]

end

][%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>,
[%R%?[N]]%.q:COMMENT%,
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]%.q:OWNER%, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE%, [%R%?[N]]</span><span>'</span><span>column</span><span>'</span><span>, [%R%?[N]]%.q:COLUMN%
:select @CurrentUser = user_name()
[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]</span><span>'</span>MS_Description<span>'</span><span>, 
[%R%?[N]]%.q:COMMENT%,
[%R%?[N]]</span><span>'</span><span>user</span><span>'</span><span>, [%R%?[N]]@CurrentUser, [%R%?[N]]</span><span>'</span><span>table</span><span>'</span><span>, [%R%?[N]]%.q:TABLE%, [%R%?[N]]</span><span>'</span><span>column</span><span>'</span><span>, [%R%?[N]]%.q:COLUMN%
]</span>

修改之后 使用Generate Database 生成的SQL便可在SQL 2005/2008下执行 不在报找不到sysproperties 的错误。

大功告成！！！

 

作者：记忆逝去的青春 出处：http://www.cnblogs.com/lukun/http://www.cnblogs.com/lukun/ 联系我，非常感谢。