sysproperties 对象是是以前老版本的sql server的对象,在2005以后就删掉了此对象,改为 sys.extended_properties,所以,需要调整 PowerDesigner 生成的建表语句的模版
首先,TableComment 模版数据,修改在PowerDesigner 下进入 Database -> Edit Current DBMS -> General -> Script -> Objects -> Table -> TableComment 目录
下面是源生成建表的模版数据
下面是新的内容(红色部分即为调整的内容)
其次,更改 ColumnComment, 修改在 PowerDesigner 下进入 Database -> Edit Current DBMS -> General -> Script -> Objects -> Column -> ColumnComment 目录
下面是新的内容
(红色部分即为调整的内容
)
修改完成后,再打开建表的语句,就编程新的模版信息了。
首先,TableComment 模版数据,修改在PowerDesigner 下进入 Database -> Edit Current DBMS -> General -> Script -> Objects -> Table -> TableComment 目录
下面是源生成建表的模版数据
- [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%
- ]
- [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%
- ]
其次,更改 ColumnComment, 修改在 PowerDesigner 下进入 Database -> Edit Current DBMS -> General -> Script -> Objects -> Column -> ColumnComment 目录
- [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%
- ]
- [if exists (select 1
- from sys.extended_properties
- where major_id = object_id('[%QUALIFIER%]%TABLE%')
- and value = [%R%?[N]]%.q:COMMENT%)
- 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%
- ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30150152/viewspace-1454979/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30150152/viewspace-1454979/