在Sql Server中查询字段信息的语句:
select a.name as TableName,
b.name as ColumnName,
case when h.id is not null then'pk' else 'no' end as PrimaryKey,
type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as Type,
b.Length,
case b.isnullable when 0 then 'N' else 'Y' end as [ColIsNull],
isnull(e.text,'') as [DefaultValue],
isnull(c.value,'') as Descript
from syscolumns b
left join sysobjects a on b.id=object_id(a.name)
left join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
left join syscomments e on b.cdefault = e.id
left join sysproperties c on b.id = c.id and b.colid = c.smallid
where a.xtype='U' and a.status > 0
order by a.name,b.colorder