SQL Server表和字段的说明
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
= i_s.TABLE_NAME,
= i_s.COLUMN_NAME,
= 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
= OBJECT_NAME(c.object_id),
= c.name,
= 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. 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. '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 表
页:
[1]