select c.name as 字段名
,t.name 数据类型
,c.prec as 长度
,p.value as 字段说明
,m.text as 默认值
from syscolumns c
inner join
systypes t
on c.xusertype=t.xusertype
left join
sysproperties p
on c.id=p.id and c.colid = p.smallid
left join
syscomments m
on c.cdefault=m.id
where objectproperty(c.id,'IsUserTable')=1
and object_id('你要查的表')=id
select a.name ,b.name ,a.length, a.isnullable
from syscolumns a, systypes b,sysobjects d
where a.xtype=b.xusertype and a.id=d.id and d.xtype='u'
and a.id=object_id('pre_qyckmyb')
1:sp_columns 'yourTableName'
2:
我一般用这个视图
Create view fielddesc
as
select o.name as oname, c.name as cname,convert(varchar(30),p.value) as value,p.smallid as psmallid,t.name as tname
from syscolumns c
join systypes t on c.xtype = t.xtype
join sysobjects o on o.id=c.id
left join sysproperties p on p.smallid=c.colid and p.id=o.id
where o.xtype='U'
-- 调用: select * from fielddesc where oname = 'yourTableName'