The query below shows indices with the same fields, and also indices with the same starting subsets of fields. Review this result of this query to identify duplicates - i.e., indices with the same fields and the same direction.
with recursive r
as (
select
ri.rdb$relation_name tname
,ri.rdb$index_name iname
,coalesce(ri.rdb$unique_flag,0) is_uniq
,coalesce(ri.rdb$index_type,0) is_desc
,ri.rdb$segment_count seg_cnt
,rs.rdb$field_position fpos
,rs.rdb$field_name fname
,coalesce(rc.rdb$constraint_name,'') ct_name
,coalesce(rc.rdb$constraint_type, '') constraint_type
,',' || cast( trim(rs.rdb$field_name) as varchar(8190) ) as idx_key
from rdb$indices ri
left join rdb$index_segments rs on ri.rdb$index_name = rs.rdb$index_name
left join rdb$relation_constraints rc on rs.rdb$index_name = rc.rdb$index_name
where
--ri.rdb$relation_name='TEST' and
ri.rdb$index_inactive is distinct from 1
and rs.rdb$field_position = 0
union all
select
ri.rdb$relation_name tname
,ri.rdb$index_name iname
,r.is_uniq
,r.is_desc
,r.seg_cnt
,rs.rdb$field_position
,rs.rdb$field_name fname
,r.ct_name
,r.constraint_type
,r.idx_key || ',' || trim(rs.rdb$field_name )
from rdb$indices ri
join rdb$index_segments rs on ri.rdb$index_name = rs.rdb$index_name
join r on ri.rdb$relation_name = r.tname
and ri.rdb$index_name = r.iname
and rs.rdb$field_position = r.fpos+1
)
--select * from r
,m as (
select
tname
,iname
,constraint_type
,is_uniq
,is_desc
,seg_cnt
,idx_key
from r where fpos=seg_cnt-1
)
select
a.tname table_name
,a.iname a_index_name
,a.is_uniq a_is_uniq
,iif(a.is_desc=1,'DESC','ASC') a_sort
,a.constraint_type a_constraint_type
,b.iname b_index_name
,b.is_uniq b_is_uniq
,iif(b.is_desc=1,'DESC','ASC') b_sort
,b.constraint_type b_constraint_type
,substring(a.idx_key from 2) a_index_key
,substring(b.idx_key from 2) b_index_key
from m a
join m b on
a.tname = b.tname
and a.is_desc = b.is_desc
and a.seg_cnt >= b.seg_cnt
and a.iname <> b.iname
and a.idx_key || ',' starting with b.idx_key || ','
and not( a.idx_key = b.idx_key
and (
a.constraint_type = '' and b.constraint_type in ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')
or
a.constraint_type = '' and b.constraint_type = '' and a.iname > b.iname
)
)
order by table_name, a_index_name, b_index_name
;