Mssql 更新字段注释

修改注释

declare @CurrentUser sysname
select @CurrentUser = user_name()
EXEC sp_updateextendedproperty 'MS_Description',
'注释内容','user', @CurrentUser, 'table', 
'表名', 'column', '列名'
go

更新注释

declare @con SQL_VARIANT
declare @CurrentUser sysname
select @CurrentUser = user_name()
set @con = (SELECT  C.value 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 = 'MODEL_COLLECT' AND B.name = 'IS_USABLE');
--如果有注释，就修改；否则就添加注释
IF (@con IS NOT NULL)
    begin 
        EXEC sp_updateextendedproperty 'MS_Description',
        '是否启用，系统字典代码是[IS_USABLE]','user', @CurrentUser, 'table', 
        'MODEL_COLLECT', 'column', 'IS_USABLE'
    end
else
    begin
        EXEC sp_addextendedproperty 'MS_Description',
        '是否启用，系统字典代码是[IS_USABLE]','user', @CurrentUser, 'table', 
        'MODEL_COLLECT', 'column', 'IS_USABLE'
    end
go