> > for some reason I need to get a list of user-defined indexes
> > for a certain table. By "user-defined indexes" I mean everything that
> > originated from a CREATE INDEX DDL statement.
> >
> > I had a look into RDB$INDICES, assuming to be able to use the
> > RDB$SYSTEM_FLAG for my purpose, but it is 1 for system indexes
> > (meaning indexes on system tables) and NULL otherwise. So my
> > user-defined indexes have the same value in RDB$SYSTEM_FLAG as my
> > RDB$PRIMARY*, RDB$FOREIGN*, etc. indexes.
> >
> > My second chance would be to filter on the index name (not
> > RDB$INDEX_NAME like 'RDB$%'), but no luck there, since Firebird 1.5 no
> > longer uses the RDB$ prefix when a named constraint is defined. I
> > think the information I need is a pretty common requirement, so I am
> > amazed to see it cannot be extracted. Does anyone have a way to
> > suggest?
>
> This might do the trick:
>
> select
> *
> from
> rdb$indices i
> where
> (i.rdb$system_flag = 0 or i.rdb$system_flag is null)
> and not exists (select 1 from rdb$relation_constraints r where
> r.rdb$index_name = i.rdb$index_name)

Exactly. This filters indices not created by constraints. Easy :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com