从Sql server 2008获取表字段属性信息，注释信息

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'
	)

[url]http://my.oschina.net/Kenyon/blog/55433[/url]
今天无意中在网上发现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]