当前目录 查询库中索引

查询索引名称，字段：

select
    t.tabname,
    trim( case when i.part1 > 0 then( select colname from syscolumns where colno = i.part1 and tabid = i.tabid ) else '' end )|| trim( case when i.part2 > 0 then( select ',' || colname from syscolumns where colno = i.part2 and tabid = i.tabid ) else '' end )|| trim( case when i.part3 > 0 then( select ',' || colname from syscolumns where colno = i.part3 and tabid = i.tabid ) else '' end )|| trim( case when i.part4 > 0 then( select ',' || colname from syscolumns where colno = i.part4 and tabid = i.tabid ) else '' end )|| trim( case when i.part5 > 0 then( select ',' || colname from syscolumns where colno = i.part5 and tabid = i.tabid ) else '' end )|| trim( case when i.part6 > 0 then( select ',' || colname from syscolumns where colno = i.part6 and tabid = i.tabid ) else '' end )|| trim( case when i.part7 > 0 then( select ',' || colname from syscolumns where colno = i.part7 and tabid = i.tabid ) else '' end )|| trim( case when i.part8 > 0 then( select ',' || colname from syscolumns where colno = i.part8 and tabid = i.tabid ) else '' end )|| trim( case when i.part9 > 0 then( select ',' || colname from syscolumns where colno = i.part9 and tabid = i.tabid ) else '' end )|| trim( case when i.part10 > 0 then( select ',' || colname from syscolumns where colno = i.part10 and tabid = i.tabid ) else '' end )|| trim( case when i.part11 > 0 then( select ',' || colname from syscolumns where colno = i.part11 and tabid = i.tabid ) else '' end )|| trim( case when i.part12 > 0 then( select ',' || colname from syscolumns where colno = i.part12 and tabid = i.tabid ) else '' end )|| trim( case when i.part13 > 0 then( select ',' || colname from syscolumns where colno = i.part13 and tabid = i.tabid ) else '' end )|| trim( case when i.part14 > 0 then( select ',' || colname from syscolumns where colno = i.part14 and tabid = i.tabid ) else '' end )|| trim( case when i.part15 > 0 then( select ',' || colname from syscolumns where colno = i.part15 and tabid = i.tabid ) else '' end )|| trim( case when i.part16 > 0 then( select ',' || colname from syscolumns where colno = i.part16 and tabid = i.tabid ) else '' end ) index_cols,
    i.idxname
    from
    systables t  join sysindexes i on
    t.tabid = i.tabid
where
    t.tabid > 99
    and t.tabtype = 'T'
order by
    1,2,3

输出示例：

tabname     t
index_cols  co1,col4
idxname     idx1

查看表上索引及行数、锁模式信息,testdb更改为需要查询的库名：

select
    t.tabname,
    t.created as tabcreated,
    t.nrows,
    tmpt.nrows as realrows,
    t.locklevel,
    t.ustlowts,
    i.idxname,
    trim( case when i.part1 > 0 then( select colname from syscolumns where colno = i.part1 and tabid = i.tabid ) else '' end )|| trim( case when i.part2 > 0 then( select ',' || colname from syscolumns where colno = i.part2 and tabid = i.tabid ) else '' end )|| trim( case when i.part3 > 0 then( select ',' || colname from syscolumns where colno = i.part3 and tabid = i.tabid ) else '' end )|| trim( case when i.part4 > 0 then( select ',' || colname from syscolumns where colno = i.part4 and tabid = i.tabid ) else '' end )|| trim( case when i.part5 > 0 then( select ',' || colname from syscolumns where colno = i.part5 and tabid = i.tabid ) else '' end )|| trim( case when i.part6 > 0 then( select ',' || colname from syscolumns where colno = i.part6 and tabid = i.tabid ) else '' end )|| trim( case when i.part7 > 0 then( select ',' || colname from syscolumns where colno = i.part7 and tabid = i.tabid ) else '' end )|| trim( case when i.part8 > 0 then( select ',' || colname from syscolumns where colno = i.part8 and tabid = i.tabid ) else '' end )|| trim( case when i.part9 > 0 then( select ',' || colname from syscolumns where colno = i.part9 and tabid = i.tabid ) else '' end )|| trim( case when i.part10 > 0 then( select ',' || colname from syscolumns where colno = i.part10 and tabid = i.tabid ) else '' end )|| trim( case when i.part11 > 0 then( select ',' || colname from syscolumns where colno = i.part11 and tabid = i.tabid ) else '' end )|| trim( case when i.part12 > 0 then( select ',' || colname from syscolumns where colno = i.part12 and tabid = i.tabid ) else '' end )|| trim( case when i.part13 > 0 then( select ',' || colname from syscolumns where colno = i.part13 and tabid = i.tabid ) else '' end )|| trim( case when i.part14 > 0 then( select ',' || colname from syscolumns where colno = i.part14 and tabid = i.tabid ) else '' end )|| trim( case when i.part15 > 0 then( select ',' || colname from syscolumns where colno = i.part15 and tabid = i.tabid ) else '' end )|| trim( case when i.part16 > 0 then( select ',' || colname from syscolumns where colno = i.part16 and tabid = i.tabid ) else '' end ) index_cols,
    i.nunique
from
    systables t left join sysindexes i on
    t.tabid = i.tabid join(
        select
            tabname,
            sum( ti_nrows ) as nrows
        from
            sysmaster:systabnames tn join sysmaster:systabinfo ti on
            ti.ti_partnum = tn.partnum
        where
            dbsname = 'testdb'  --此处修改为需要查询的库名
        group by
            tabname
    ) as tmpt on
    t.tabname = tmpt.tabname
where
    t.tabid > 99
    and t.tabtype = 'T'
order by
    4 desc,
    1;

输出示例：

tabname     t
tabcreated  2023 12月 12日
nrows       0.00
realrows    0
locklevel   R
ustlowts    2023-12-12 10:09:09.76997
idxname     idx1
index_cols  co1,col4
nunique     0.00