MsSql批量修改备注导出批量修改字段注释语句

最新推荐文章于 2024-12-25 10:38:50 发布

飓风部落格

最新推荐文章于 2024-12-25 10:38:50 发布

阅读量934

点赞数 1

CC 4.0 BY-SA版权

分类专栏：技术 SQL技术文章标签： sql

本文链接：https://blog.youkuaiyun.com/jufeng9318/article/details/84981485

技术同时被 2 个专栏收录

49 篇文章

订阅专栏

SQL技术

2 篇文章

订阅专栏

本文介绍了一种在MsSql中批量修改字段注释的方法，通过执行特定的SQL语句，可以有效地更新数据库表中各字段的描述信息，适用于需要大规模调整数据库元数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MsSql导出批量修改字段注释语句

SELECT A.name AS table_name, B.name AS column_name, C.value AS column_description
, '
if exists (
SELECT
1
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
where A.name =''' + A.name + ''' and B.name =''' + B.name + ''' and C.value is not null)
begin
EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N''' + CAST(C.value AS varchar(500)) + ''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''' + CAST(A.name AS varchar(500)) + ''', @level2type=N''COLUMN'',@level2name=N''' + CAST(B.name AS varchar(500)) + '''' + '

end
else
begin
EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''' + CAST(C.value AS varchar(500)) + ''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''' + CAST(A.name AS varchar(500)) + ''', @level2type=N''COLUMN'',@level2name=N''' + CAST(B.name AS varchar(500)) + '''' + '
end
GO
' AS bb
FROM sys.tables A
   INNER JOIN sys.columns B ON B.object_id = A.object_id
   LEFT JOIN sys.extended_properties C
   ON C.major_id = B.object_id
       AND C.minor_id = B.column_id
WHERE C.value IS NOT NULL
ORDER BY table_name