gbase8s之数据字典查询

查看一个数据库中比如 2000张表  看看每张表用了什么样的主键定义。
select tabname,idxcloumn
from ( select 
decode(a.constrtype,'P',b.tabname) as tabname,
decode(c.part1, 0,'',( select decode(colno+part1, 0,colname||' desc',colname)
from syscolumns where tabid=c.tabid and colno= abs(c.part1) ))||
decode(c.part2, 0,'',','||(select decode(colno+part2, 0,colname||' desc',colname)
from syscolumns where tabid=c.tabid and colno= abs(c.part2) ))||
decode(c.part3, 0,'',','||(select decode(colno+part3, 0,colname||' desc',colname)
from syscolumns where tabid=c.tabid and colno= abs(c.part3) )) as idxcloumn
from sysconstraints a ,systables b ,sysindexes c
where a.tabid>=100 and a.constrtype ='P' and a.tabid=b.tabid and b.tabtype='T' and
a.idxname=c.idxname and a.tabid=c.tabid );

或者生成视图 

create view show_tables as
  select  tmp.tabid,tmp.tabname,tmp.colname,tmp.coltype_name,pi.constrtype,
            CASE d.type   
                WHEN 'L' THEN gbasedbt.get_default_value(tmp.coltype, tmp.extended_id, tmp.collength, d.default::lvarchar(256))::VARCHAR(254)