SELECT a.column_id,a.name,b.name AS datatye,a.max_length,a.precision,a.scale,
a.is_identity,a.is_nullable,a.is_computed,
g.definition AS defaultValue,
c.definition AS expression,
d.value AS comment,e.indid AS indexId,f.name AS indexName,f.is_primary_key,f.is_unique
from sys.columns a
LEFT JOIN sys.types b ON a.system_type_id=b.system_type_id
LEFT JOIN sys.computed_columns c ON a.column_id=c.column_id
LEFT JOIN sys.extended_properties d ON d.major_id=a.object_id AND minor_id=a.column_id
LEFT JOIN sysindexkeys e ON a.object_id=e.id AND a.column_id=e.colid
LEFT JOIN sys.indexes f ON f.object_id=a.object_id AND f.index_id=e.indid AND e.colid=a.column_id
LEFT JOIN sys.default_constraints g ON g.object_id=a.default_object_id
WHERE a.object_id= OBJECT_ID('test')
SQL 2008以上版本,以下的不知道支不支持
可以查询字段的数据类型,长度,精度,自增列,是否为空,计算列及表达式,默认值,字段说明等信息