Nando,

> 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)



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2 (NEW!)
Upscene Productions
http://www.upscene.com