sql server sql语句判断是否有表备注并进行新增或修改

sql server的表备注修改比较麻烦，不像oracle：comment on table xxx is 'xxx';

--新增表注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'备注',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名';
--或
EXEC sp_addextendedproperty 'MS_Description','备注','SCHEMA','dbo','TABLE','表名';

--修改表注释
EXEC sys.sp_updateextendedproperty @name=N'MS_Description',@value=N'备注',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名';
--或
EXEC sp_updateextendedproperty 'MS_Description','备注','SCHEMA','dbo','TABLE','表名';

--删除表注释，EXEC同EXECUTE
EXEC sys.sp_dropextendedproperty @name=N'MS_Description',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名';
--或
EXECUTE sp_dropextendedproperty 'MS_Description','SCHEMA','dbo','TABLE','表名';

 

没有表注释时只能执行新增的存储过程sys.sp_addextendedproperty，使用修改的会报错，反之亦然。所以判断是否存在表注释后进行修改或新增

declare @havedesc bit
set @havedesc=(select count(0) from sys.extended_properties where major_id=object_Id('表名') and minor_id=0)
--print @havedesc
if @havedesc>0
    begin
        EXEC sys.sp_updateextendedproperty @name=N'MS_Description',@value=N'备注1',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名'
    end
else
    begin
        EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'备注2',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名'
    end

 

ps：执行新增的时候EXEC sp_addextendedproperty 'MS_Description','备注','SCHEMA','dbo','TABLE','表名';改备注为aaa改错了把MS_Description改为了aaa，也执行成功了，查询extended_properties结果如下，感觉是把注释作为一个属性了，有说是扩展属性的

 

1    OBJECT_OR_COLUMN    1154103152    0    aaa    备注
1    OBJECT_OR_COLUMN    1154103152    0    MS_Description    asdf
1    OBJECT_OR_COLUMN    1154103152    1    MS_Description    主键

 

 参考：

新增、修改、删除表注释：https://www.cnblogs.com/xingyadian/p/10030793.html  https://www.cnblogs.com/straw/p/5964899.html

变量赋值：https://www.cnblogs.com/Xujg/p/3338076.html

if else写法：https://www.cnblogs.com/EasonJim/p/6136957.html

也可以先判断是否存在表备注，存在则删除，再添加表备注：https://blog.csdn.net/caozhangcaoluo/article/details/21467835

扩展属性：https://www.cnblogs.com/fishparadise/p/4743937.html