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

sqlserver 2008获得表的注释和字段的注释

Sql代码

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'

)

http://my.oschina.net/Kenyon/blog/55433
今天无意中在网上发现Sqlserver有一个扩展属性系统表sysproperties，因为只接触过MSSQL2005及以后的版本，在生产库2008版本及联机文档上搜了下都找不到这个系统表，后来发现这个系统表在2005版本后就被另一个系统表sys.extended_properites所代替。

查看表的扩展属性：

Sql代码

select object_id from sys.sysobjects where name = table_name;

select * from sys.extended_properties where major_id = object_id;

扩展属性中有一name值是MS_Description，这个值是查看备注信息的。

1 2	Sql代码 select * from sys.extended_properties where major_id = object_id and name = 'MS_Description';

至于属性的意思,参考:http://msdn.microsoft.com/zh-cn/library/ms177541(v=sql.105).aspx
class,tinyint, 标识其上存在属性的项类。可以是下列值之一：
0 = 数据库
1 = 对象或列
2 = 参数
3 = 架构
4 = 数据库主体
5 = 程序集
6 = 类型
7 = 索引
10 = XML 架构集合
15 = 消息类型
16 = 服务约定
17 = 服务
18 = 远程服务绑定
19 = 路由
20 = 数据空间（文件组或分区方案）
21 = 分区函数
22 = 数据库文件
27 = 计划指南

class_desc,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

major_id,int,其上存在扩展属性的项 ID，根据项类进行解释。对于大多数项，该 ID 适用于类所表示的项。下列是非标准主 ID 的解释：
如果 class 为 0，则 major_id 始终为 0。
如果 class 为 1、2 或 7，则 major_id 为 object_id。

minor_id,int,其上存在扩展属性的项辅助 ID，根据项类进行解释。对于大多数项，ID 为 0；否则，ID 为下列值之一：
如果 class = 1，则 minor_id 在项为列的情况下等于 column_id，在项为对象的情况下等于 0。
如果 class = 2，则 minor_id 为 parameter_id。
如果 class = 7，则 minor _id 为 index_id。

name,sysname,属性名，其 class、major_id 和 minor_id 是唯一的。

value,sql_variant,扩展属性的值。

http://hi.baidu.com/wyg_bd/item/9c153f3d149300697d034b4c
在SqlServer中如何得到列的描述信息_sys.SysColumns和sys.extended_properties关系，sql-promptx下载
Sql开发工具下载地址 sql-promptx

Sql代码

SELECT a.*,b.value AS'描述内容' FROMsys.syscolumns a

LEFTJOINsys.extended_propertiesb ON a.id=b.major_id

WHERE a.id=(SELECTOBJECT_ID('表名称'))

方法二：

Sql代码

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

1 2	Sql代码 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

Sql代码

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

Sql代码

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'

http://space.itpub.net/16436858/viewspace-624807

Sql代码

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

http://coding-in.net/445/

Sql代码

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

Sql代码

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

Sql代码

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

不静之心

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

发表评论取消回复

访问信息

功能

近期评论

不静之心

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

发表评论 取消回复

访问信息

功能

近期评论

发表评论取消回复