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

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