获取、增加、修改、删除sqlserver字段描述
Select o.name AS tableName, c.name AS columnName, p.[value] AS Description
FROM sysproperties p INNER JOIN
sysobjects o ON o.id = p.id INNER JOIN
syscolumns c ON p.id = c.id AND p.smallid = c.colid
Where (p.name = "MS_Description") AND (c.name = "province") AND (o.name = "ip_address")
orDER BY o.name
选取表ip_address中 字段province的描述
--创建表及描述信息
create table 表(a1 varchar(10),a2 char(2))
--为表添加描述信息
EXECUTE sp_addextendedproperty N"MS_Description", "人员信息表", N"user", N"dbo", N"table", N"表", NULL, NULL
--为字段a1添加描述信息
EXECUTE sp_addextendedproperty N"MS_Description", "姓名", N"user", N"dbo", N"table", N"表", N"column", N"a1"
EXECUTE sp_addextendedproperty N"MS_Description", "测试", N"user", N"dbo", N"table", N"HR_Employees", N"column", N"test"
--为字段a2添加描述信息
EXECUTE sp_addextendedproperty N"MS_Description", "性别", N"user", N"dbo", N"table", N"表", N"column", N"a2"
--更新表中列a1的描述属性:
EXEC sp_updateextendedproperty "MS_Description","字段1","user",dbo,"table","表","column",a1
--删除表中列a1的描述属性:
EXEC sp_dropextendedproperty "MS_Description","user",dbo,"table","表","column",a1
--删除测试
drop table 表