select a.name 表名,
b.name 字段名,
case c.name when 'numeric' then 'numeric(' + convert(varchar,b.length) + '，' + convert(varchar,b.xscale) + ')'
when 'char' then 'char(' + convert(varchar,b.length) + ')'
when 'varchar' then 'varchar(' + convert(varchar,b.length) + ')'
else c.name END AS 字段类型
from sysobjects a,syscolumns b,systypes c where a.id=b.id
and a.name='表名' and a.xtype='U'
and b.xtype=c.xtype
效果如图
：