sqlserver 2008获得表的注释和字段的注释
select a.name, a.object_id as table_id,b.minor_id, b.value
from sys.tables a left join sys.extended_properties b on a.object_id=b.major_id
where a.name='TABLE3' and b.minor_id=0 and b.name = 'MS_Description'
and a.schema_id=(
select schema_id from sys.schemas where name='dbo'
)
select a.name as table_name, b.name as column_name, c.value as remarks
from sys.tables a left join sys.columns b on a.object_id=b.object_id
left join sys.extended_properties c on a.object_id=c.major_id
where a.name='TABLE3' and c.minor_id<>0 and b.column_id=c.minor_id
and a.schema_id=(
select schema_id from sys.schemas where name='dbo'
)
今天无意中在网上发现Sqlserver有一个扩展属性系统表sysproperties，因为只接触过MSSQL2005及以后的版本，在生产库2008版本及联机文档上搜了下都找不到这个系统表，后来发现这个系统表在2005版本后就被另一个系统表sys.extended_properites所代替。
查看表的扩展属性：
select object_id from sys.sysobjects where name = table_name;
select * from sys.extended_properties where major_id = object_id;
扩展属性中有一name值是MS_Description，这个值是查看备注信息的。
select * from sys.extended_properties where major_id = object_id and name = 'MS_Description';
至于属性的意思,参考:[url]http://msdn.microsoft.com/zh-cn/library/ms177541(v=sql.105).aspx[/url]
[color=red]class[/color],tinyint, 标识其上存在属性的项类。可以是下列值之一：
0 = 数据库
1 = 对象或列
2 = 参数
3 = 架构
4 = 数据库主体
5 = 程序集
6 = 类型
7 = 索引
10 = XML 架构集合
15 = 消息类型
16 = 服务约定
17 = 服务
18 = 远程服务绑定
19 = 路由
20 = 数据空间（文件组或分区方案）
21 = 分区函数
22 = 数据库文件
27 = 计划指南
[color=red]class_desc[/color],nvarchar(60),其上存在扩展属性的类的说明。可以是下列值之一：
DATABASE
OBJECT_OR_COLUMN
PARAMETER
SCHEMA
DATABASE_PRINCIPAL
ASSEMBLY
TYPE
INDEX
XML_SCHEMA_COLLECTION
MESSAGE_TYPE
SERVICE_CONTRACT
SERVICE
REMOTE_SERVICE_BINDING
ROUTE
DATASPACE
PARTITION_FUNCTION
DATABASE_FILE
PLAN_GUIDE
[color=red]major_id[/color],int,其上存在扩展属性的项 ID，根据项类进行解释。对于大多数项，该 ID 适用于类所表示的项。下列是非标准主 ID 的解释：
如果 class 为 0，则 major_id 始终为 0。
如果 class 为 1、2 或 7，则 major_id 为 object_id。
[color=red]minor_id[/color],int,其上存在扩展属性的项辅助 ID，根据项类进行解释。对于大多数项，ID 为 0；否则，ID 为下列值之一：
如果 class = 1，则 minor_id 在项为列的情况下等于 column_id，在项为对象的情况下等于 0。
如果 class = 2，则 minor_id 为 parameter_id。
如果 class = 7，则 minor _id 为 index_id。
[color=red]name[/color],sysname,属性名，其 class、major_id 和 minor_id 是唯一的。
[color=red]value[/color],sql_variant,扩展属性的值。
[url]http://hi.baidu.com/wyg_bd/item/9c153f3d149300697d034b4c[/url]
在SqlServer中如何得到列的描述信息_sys.SysColumns和sys.extended_properties关系，sql-promptx下载
Sql开发工具下载地址 sql-promptx
SELECT a.*,b.value AS'描述内容' FROMsys.syscolumns a
LEFTJOINsys.extended_propertiesb ON a.id=b.major_id
WHERE a.id=(SELECTOBJECT_ID('表名称'))
方法二：
select
TableName = tbl.table_schema + '.' + tbl.table_name,
TableDescription = prop.value,
ColumnName = col.column_name,
ColumnDataType = col.data_type,
DescriptionValue=prop.value
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col
ON col.table_name = tbl.table_name
LEFT JOIN sys.extended_properties prop
ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND prop.minor_id = 0
AND prop.name = 'MS_Description'
WHERE tbl.TABLE_NAME = '表名称'
二、 取得column_id，system_type_id，user_type_id,default_object_id
SELECT c.column_id，c.system_type_id，user_type_id, default_object_id FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.object_id = t.object_id WHERE t.name = 'TabT' AND c.name = 'conlumName of TabT'
三，取得key_constraint_id
SELECT k.object_id FROM sys.key_constraintsAS k INNER JOIN sys.indexes AS i ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id INNER JOIN sys.index_columnsAS x ON i.object_id = x.object_id AND i.index_id = x.index_id INNER JOIN sys.columnsAS c ON x.object_id = c.object_id AND x.column_id = c.column_id INNER JOIN sys.tables AS t ON c.object_id = t.object_id AND k.schema_id = t.schema_id WHERE i.is_disabled = 0 AND t.name = 'TabT' AND c.name = 'conlumName of TabT'
四、foreign_key_constraint_id
SELECT f.object_id FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS l ON f.object_id = l.constraint_object_id INNER JOIN sys.columns AS c ON f.parent_object_id = c.object_id AND l.parent_object_id = c.object_id AND l.parent_column_id = c.column_id INNER JOIN sys.tables AS t ON c.object_id = t.object_id AND f.schema_id = t.schema_id WHERE f.is_disabled = 0 AND t.name = 'TabT' AND c.name = 'conlumName of TabT'
[url]http://space.itpub.net/16436858/viewspace-624807[/url]
select b.[value] from sys.columns a left join sys.extended_properties b on a.object_id=b.major_id
and a.column_id=b.minor_id inner join sysobjects c on a.column_id=c.id
and a.[name]='列名' and c.[name]='表名'
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
--where d.name='orders' --如果只查询指定表,加上此条件
order by a.id,a.colorder
[url]http://coding-in.net/445/[/url]
select
INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME
from INFORMATION_SCHEMA.TABLES
order by INFORMATION_schema.TABLES.TABLE_NAME
[img]http://coding-in.net/blog/wp-content/uploads/Information_Schema_Sql_1.png[/img]
select
INFORMATION_schema.COLUMNS.TABLE_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_schema.COLUMNS.DATA_TYPE,
INFORMATION_schema.COLUMNS.IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
order by INFORMATION_schema.COLUMNS.TABLE_NAME
[img]http://coding-in.net/blog/wp-content/uploads/Information_Schema_Sql_2.png[/img]
select
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
,INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
,INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE
,INFORMATION_SCHEMA.COLUMNS.DATA_TYPE
,INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
from INFORMATION_SCHEMA.COLUMNS
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE
on INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG = INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG
and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME
and INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
order by INFORMATION_schema.COLUMNS.TABLE_NAME
[img]http://coding-in.net/blog/wp-content/uploads/Information_Schema_Sql_3.png[/img]