SqlServer对数据库字段说明进行操作

--查看字段说明

SELECT t.[name] AS [表名],c.[name] AS [字段名],cast(ep.[value]

as varchar(100)) AS [字段说明]

FROM sys.tables AS t INNER JOIN sys.columns

AS c ON t.object_id = c.object_id LEFT JOIN sys.extended_properties AS ep

ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1   

and t.[name]='表名' and c.[name] in ('字段名','字段名')

--新增字段说明

EXEC

sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'字段说明' , @level0type=N'SCHEMA',@level0name=N'dbo',

@level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',

@level2name=N'字段名'

GO

--修改字段说明

BEGIN TRANSACTION

GO

DECLARE @v sql_variant

SET @v = N'字段说明'

EXECUTE sys.sp_updateextendedproperty N'MS_Description',

@v, N'SCHEMA',N'dbo',N'TABLE',N'表名', N'COLUMN', N'字段名'

GO

COMMIT