造成此问题的原因是由于Sql 2005 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致,微软的目的不再去猜测
网上有二种解决方式 但不符合本人的需要 以下是通过创建sysproperties视图,以及改造powerdesigner sql语句生成模板实现本人直接用powerdesigner生成创建数据库SQL
方法实现简单,在此做个记录
-------------------------------------------------
Sql 2005 Exec the Sql 创建View 'sysproperties'
-------------------------------------------------
-
ifexists(select1
-
fromsysobjects
-
wherename='sysproperties'
-
andxtype='V')
-
begin
-
DROPVIEWsysproperties
-
end
-
GO
-
CREATEVIEWsysproperties
-
AS
-
SELECTA.nameAsTableName,A.idAsTableID,B.NameAsColName,B.colidAsColID,B.xtypeAsColType,C.nameAsPropName,C.ValueAsPropValue
-
FROMsysobjectsAsA
-
INNERJOINsyscolumnsAsBONA.id=B.id
-
INNERJOINsys.extended_propertiesAsCONC.major_id=A.idAND(minor_id=B.colid)
-
-
GO
-------------------------------------------------
Modified Table TableComment
修改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%
]
-------------------------------------------------
Modified Column ColComment
修改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%
]
修改之后 使用Generate Database 生成的SQL便可在SQL 2005下执行 不在报找不到sysproperties 的错误
仅解决找不到sysproperties 的错误 其它错误欢迎讨论
以下是源模板备份
-------------------------------------------------
Old Table TableComment Bak
-------------------------------------------------
[if exists (select 1
from sysproperties
where id = object_id('[%QUALIFIER%]%TABLE%')
and type = 3)
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%
]
-------------------------------------------------
Old Column ColComment Bak
-------------------------------------------------
[if exists (select 1
from sysproperties
where id = object_id('[%QUALIFIER%]%TABLE%')
and type = 4)
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%
]
分享到:
相关推荐
解决PowerDesigner 16 找不到sysproperties表的问题。
PowerDesigner16.5+PostgresSQL
如何让PowerDesigner 自动生成 SQL Server 2000的表和列注释的脚本 ======================================= 1、将本包中的模板文件(Microsoft SQL Server 2000(带...
powerdesigner的使用 sql语句 sql语句的使用
使用PowerDesigner设计SQL Server数据库
PowerDesigner16 破解文件/注册码 PowerDesigner16 破解文件/注册码 PowerDesigner16 破解文件/注册码 PowerDesigner16 破解文件/注册码 亲测有效
使用PowerDesigner通过excel模板文件与vb可执行代码,生成创建数据库sql建表语句脚本,资源包含.vb 和 excel模板文件。
powerdesigner 16 license powerdesigner 16 license powerdesigner 16 license powerdesigner 16 license 复制到安装目录即可
PowerDesigner 16 Crack Key License 破解 注册
powerdesigner将SQL文件转换为ER图形 ,带详细图解,每一步操作都有对应的图
PowerDesigner导出的SQL带列注释,导入到MySQL中列注释不见了的处理方法
PowerDesigner中Table选择不到
PowerDesigner从数据库中导出表结构
把SQL文件反射到PowerDesigner模版中
配置方法: 替换DBMS配置文件 位置:\Sybase\PowerDesigner 12\Resource Files\DBMS\oracl10g.xdb 完成后执行反向工程即可,反向工程的时候DBMS选择Oracle version 10g就可以了
32位powerdesigner16序列号文件 安装后复制到安装目录,或替换安装目录下同名文件即可。 windows XP 2003 2008 7 均可使用,其余平台未测试
PowerDesigner根据SQL语句自动生成ER图
PowerDesigner16
使用PowerDesigner导出sqlserver2008_数据库
使用Powerdesigner生成sql脚本