SQL SERVER 扩展属性的操作方法

将数据库迁移到 Azure SQL 数据库时出现错误，不支持扩展属性“MS_Description”，因此就如何操作扩展属性进行在此记录。

查询扩展属性

SELECT *,OBJECT_NAME(major_id) AS obj_name FROM sys.extended_properties

添加扩展属性

EXEC sp_addextendedproperty

@name = N'MS_Description',

@value = N'This is a table description on [T8](2).',

@level0type = N'SCHEMA', @level0name = N'dbo',

@level1type = N'TABLE', @level1name = N'T8'

GO

修改扩展属性

EXEC sp_updateextendedproperty

@name = N'MS_Description',

@value = N'This is a column description on [id](3).',

@level0type = N'SCHEMA', @level0name = N'dbo',

@level1type = N'TABLE', @level1name = N'T8',

@level2type = N'COLUMN', @level2name = N'id'

GO

删除扩展属性

EXEC sp_dropextendedproperty

@name = N'MS_Description',

@level0type = N'SCHEMA', @level0name = N'dbo',

@level1type = N'TABLE', @level1name = N'FilterPageItem',

@level2type = N'COLUMN', @level2name = N'ColIndex'

GO