PowerDesigner SqlServer 表列描述脚本无法执行问题

1.原脚本,sp_addextendedproperty的0级别类型写的是user,这个类型在高版本中已经删除,无法使用,应该切换成SCHEMA 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('[%QUALIFIER%]%TABLE%')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = %.q:COLUMN%)
)
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%
]

 

2.PowerDesigner工作空间-新建-Physica Data Model;右键PhysicalDataModel_1,选择属性-DBMS-打开右边的Properties-Script-Column-ColumnComment

3.修改值为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('[%QUALIFIER%]%TABLE%')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = %.q:COLUMN%)
)
begin
   [%OWNER%?[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description',
   [%R%?[N]]'SCHEMA', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
:declare @CurrentUser sysname
select @CurrentUser = 'dbo'
[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description',
   [%R%?[N]]'SCHEMA', [%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]]'SCHEMA', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
:select @CurrentUser = 'dbo'
[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description',
   [%R%?[N]]%.q:COMMENT%,
   [%R%?[N]]'SCHEMA', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
]

  

4.双击设计的表,查看preview就可以拿到能执行的sql了

posted @   随意听风  阅读(32)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
TOP
点击右上角即可分享
微信分享提示