sqlserver中查询表字段的sql语句

sqlserver中的表信息字段信息这些东西也是放到系统表中的，以下sql语句用于查询某表的字段信息。

select t1.id object_id,t1.name object_name,t2.value table_comment,t3.colorder,t3.name col_name,t5.name col_type,COLUMNPROPERTY(t1.id,t3.name,'Precision') precision,COLUMNPROPERTY(t1.id,t3.name,'Scale') scale,t4.value col_comment,t3.isnullable,t3.length from 
(select id,name from sysobjects) t1
left join (select major_id,value from sys.extended_properties where class="1" and minor_id=0) t2
on t2.major_id=t1.id
left join (select id,name,xtype,length,isnullable,colorder from syscolumns) t3
on t3.id=t1.id
left join (select major_id,minor_id,value from sys.extended_properties where class="1" and minor_id<>0) t4
on t4.major_id=t1.id and t4.minor_id=t3.colorder
left join (select name,xusertype from systypes) t5
on t5.xusertype=t3.xtype
where t1.name in ('orderbill')
order by object_name,colorder

 

finished