IDC资讯中心
先创建一个视图
create view fielddesc
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as
length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c
join systypes t on c.xtype = t.xusertype
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 table_name = 你的表名
还有个更强的语句，是邹建写的，也写出来吧
select
(case when a.colorder=1 then d.name else end) n表名,
a.colorder n字段序号,
a.name n字段名,
(case when columnproperty( a.id,a.name,isidentity)=1 then √else end) n标识,
(case when (select count(*)
from sysobjects
where (name in
(select name
from sysindexes
where (id = a.id) and (indid in
(select indid
from sysindexkeys
where (id = a.id) and (colid in
(select colid
from syscolumns
where (id = a.id) and (name = a.name))))))) and
(xtype = pk))>0 then √ else end) n主键,
b.name n类型,
a.length n占用字节数,
columnproperty(a.id,a.name,precision) as n长度,
isnull(columnproperty(a.id,a.name,scale),0) as n小数位数,
(case when a.isnullable=1 then √else end) n允许空,
isnull(e.text,) n默认值,
isnull(g.[value],) as n字段说明
–into ##tx
from syscolumns a left join systypes b
on a.xtype=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 sysproperties g
on a.id=g.id and a.colid = g.smallid
order by object_name(a.id),a.colorder
