SELECT

        (case when a.colorder=1 then d.name else '' end) 表名,

        a.colorder 字段序号,

        a.name 字段名,

       g.[value] AS 字段说明

FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join sys.extended_properties g

on a.id=g.major_id AND a.colid = g.minor_id

WHERE d.[name] <>'table_desc' --你要查看的表名，注释掉，查看当前数据库所有表的字段信息

order by a.id,a.colorder

--创建表及描述信息

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'

--为字段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   表 

//更新表的备注信息

 string  updateTableDescription = @"  if(select count(1) from fn_listextendedproperty(N'MS_Description'," +

   "   N'SCHEMA',N'{2}',N'TABLE',N'{0}',NULL,NULL))>0 " +

  "    EXEC sp_updateextendedproperty N'MS_Description',N'{1}', " +

  "    N'SCHEMA',N'{2}', N'TABLE',N'{0}',NULL,NULL  " +

  "   ELSE   " +

 "   EXEC sp_addextendedproperty  N'MS_Description',N'{1}', " +

 "  N'SCHEMA',N'{2}',N'TABLE',N'{0}'   ";

//更新字段的备注信息

 string  updateColumnDescription=@"if(select count(1) from fn_listextendedproperty(N'MS_Description',N'SCHEMA', "+

       " N'{3}',N'TABLE',N'{0}',N'COLUMN', N'{1}'))>0   "+

       " EXEC sp_updateextendedproperty N'MS_Description',N'{2}',  "+

       "    N'SCHEMA',N'{3}', N'TABLE',N'{0}',N'COLUMN', N'{1}'  "+

       "  ELSE   "+

       "  EXEC sp_addextendedproperty  N'MS_Description',N'{2}', "+

        "      N'SCHEMA',N'{3}',N'TABLE',N'{0}',N'COLUMN', N'{1}'   ";

 //读取表的描述

string getTableDescription = " select value from  " +

        " fn_listextendedproperty(N'MS_Description',N'SCHEMA',N'{1}',N'TABLE'," +

        " N'{0}',NULL, NULL)  ";

//读取字段的描述

string getColumnDescription=" select objname,value from  "+

        " fn_listextendedproperty(N'MS_Description',N'SCHEMA',N'{1}',N'TABLE',"+

        " N'{0}',N'COLUMN', NULL)  ";