[Sql,Server,获取、增加、修改、删除,字段描述] 关键字段的描述正确的是

获取、增加、修改、删除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 表