Search code examples
firebird

List of all the Indexes in the firebird

I am trying to list out the all indexes from the Firebird. I am using DBworkbench tool for Firebird database. With this tool, I can see around 200 indices in my database. I want a query to list of all these indices with its related tables and column.

Solution

  • You don't need a special tool for this, just write a simple query in your favorite tool.

    The system table RDB$INDICES stores definitions of both system- and user-defined indexes. The attributes of each column belonging to an index are stored in one row of the table RDB$INDEX_SEGMENTS.

    This query will return information about non-system indexes:

    select * from RDB$INDICES where RDB$SYSTEM_FLAG = 0;

    To get information about name, table name and field name of non-system indexes may use:

    select 
    rdb$indices.rdb$index_name,
    rdb$indices.rdb$relation_name,
    rdb$index_segments.rdb$field_name
from rdb$index_segments
   right outer join rdb$indices on (rdb$index_segments.rdb$index_name = rdb$indices.rdb$index_name)
where 
   (
      (rdb$indices.rdb$system_flag = 0)
   )