INFORMIX查询标准表结构SQL

-- 规范SQL

select

a.tabname as tabname_en,

'' tabname_cn,

b.colno,

b.colname as colname_en,

'' colname_cn,

case b.coltype

when '0'

then 'varchar2('||collength||')'

when '1'

then 'integer'

when '2'

then 'integer'

when '3'

then 'float'

when '4'

then 'float'

when '5'

then 'decimal('||trunc(collength/256)||','||mod(collength,256)||')'

when '6'

then 'integer'

when '7'

then 'date'

when '8'

then 'money'

when '9'

then 'null'

when '10'

then 'datetime'

when '11'

then 'byte'

when '12'

then 'text'

when '13'

then 'varchar2('||collength||')'

when '14'

then 'interval'

when '15'

then 'varchar2('||collength||')'

when '16'

then 'varchar2('||collength||')'

when '17'

then 'int8'

then 'serial8'

when '19'

then 'set'

when '20'

then 'multiset'

when '21'

then 'list'

when '22'

then 'unnamed row'

when '40'

then 'varchar2(4000)'

when '41'

then 'clob'

when '43'

then 'blob'

when '44'

then 'boolean'

when '256'

then 'varchar2('||collength||')'

when '257'

then 'integer'

when '258'

then 'integer'

when '259'

then 'float'

when '260'

then 'real'

when '261'

then 'decimal('||trunc(collength/256)||','||mod(collength,256)||')' when '262'

then 'serial'

when '263'

then 'date'

when '264'

then 'money'

when '266'

then 'datetime'

when '267'

then 'byte'

when '268'

then 'text'

when '269'

then 'varchar'

then 'interval'

when '271'

then 'varchar2('||collength||')'

when '272'

then 'varchar2('||collength||')'

when '273'

then 'int8'

when '274'

then 'serial8'

when '275'

then 'set'

when '276'

then 'multiset'

when '277'

then 'list'

when '278'

then 'unnamed row'

when '296'

then 'lvarchar'

when '297'

then 'clob'

when '298'

then 'blob'

when '299'

then 'boolean'

when '4118'

then 'named row'

end as coltypename,

case

when c.idxtype='U'

then 'Y'

else 'N'

end as KEY_TYPE,

case

when b.coltype>=256

then 'N'

else 'Y'

end as NULL_TYPE

from

systables a

left join

syscolumns b

on

a.tabid=

b.tabid

left join

sysindexes c

on

a.tabid=c.tabid

and b.colno in (c.part1,

c.part2,

c.part3,

c.part4,

c.part5,

c.part6,

c.part7,

c.part8) and idxtype='U'

where

a.tabid>99

and a.tabtype='T'

AND a.tabname = ‘test’order by

a.tabname,

b.colno