Sybase SQL查询字段类型

1、基础sql:

查看某个表中的数据字段、类型、长度

select c.id, c.colid, c.name, c.usertype, c.length from syscolumns c, sysobjects o

where o.id =c.id and o.type='U' and o.name='表名';

 

2、最终sql:

根据表名，查询表中所有字段的详细信息。包括字段名,类型,长度,是否为空,默认值,是否主键信息。
 

select     
 a.name as 字段名, 
 b.name as 字段类型,  
 a.length as 长度, 
 (case  when  a.status =8  then 'null'  else 'not mull'  end) as  是否为空, 
 isnull(e.text,'')  as 默认值 
FROM     syscolumns     a   left   join   systypes   b      
  on     a.usertype=b.usertype    
inner   join   sysobjects   d      
  on   a.id=d.id     and     d.type='U'   and     d.name<>'dtproperties'   
left   join   syscomments   e    
  on   a.cdefault=e.id    
where d.name='表名'

select isnull(c.name, 'NULL') '字段名',
t.name '字段类型',
c.length '字段长度'
from syscolumns c, systypes t, sysxtypes x
where c.id = object_id('表名')
and c.usertype *= t.usertype
and c.xtype *= x.xtid;