2005
select sys.columns.name cloname, sys.types.name typename, sys.columns.max_length length, sys.columns.is_nullable nullable, (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as descriptionfrom sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name='map_markertype' --表名order by sys.columns.column_id
补充2000
Select col.[name] as '字段名', col.[length]as '长度' , type.[name] as '类型' , pro.value as '描述' From syscolumns as col Left Join systypes as type on col.xtype = type.xtype Left Join sysProperties as pro on col.id = pro.id and col.colid = pro.smallid where col.id = (Select id From Sysobjects Where name = 'table name')
具体参见:
http://www.cnblogs.com/eflylab/archive/2008/06/23/1227838.html