1. 增加字段说明
EXEC sp_addextendedproperty ' MS_Description ' , ' some description ' , ' user ' , dbo, ' table ' , table_name, ' column ' , column_name
- Some Description ， 是要增加的说明内容
- table_name, 是表名
- column_name , 是字段名
2. 增加表的说明
EXEC sp_addextendedproperty 'MS_Description', 'some description', 'user', dbo, 'table', table_name
参数说明同上
3. 取得字段说明内容
| SQL Server 2000 | SQL Server 2005 ( 包括 express) |
| SELECT [Table Name] = i_s.TABLE_NAME, [Column Name] = i_s.COLUMN_NAME, [Description] = s.value FROM INFORMATION_SCHEMA.COLUMNS i_s LEFT OUTER JOIN sysproperties s ON s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) AND s.smallid = i_s.ORDINAL_POSITION AND s.name = 'MS_Description' WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0 -- AND i_s.TABLE_NAME = 'table_name' ORDER BY i_s.TABLE_NAME, i_s.ORDINAL_POSITION | SELECT [Table Name] = OBJECT_NAME(c.object_id), [Column Name] = c.name, [Description] = ex.value FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 -- AND OBJECT_NAME(c.object_id) = 'your_table' ORDER BY OBJECT_NAME(c.object_id), c.column_id |
4. 取得表说明
| SELECT 表名 = case when a.colorder = 1 then d.name else '' end, 表说明 = case when a.colorder = 1 then isnull(f.value, '') else '' end FROM syscolumns a inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'sys.extended_properties' left join sys.extended_properties f on a.id = f.major_id and f.minor_id = 0 Where (case when a.colorder = 1 then d.name else '' end) <>'' |
另外，还有更新表的字段说明---》
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 表
--获取某一个字段的描述SELECT *FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '表名', 'column', default)--其他变数，按照你的要求你照写即可，只要表名换成你的where objname = '字段名'
原文链接：http://www.cnblogs.com/hantianwei/archive/2011/05/16/2047543.html