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了
分类:
SqlServer
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix