The following example illustrates how to recreate database indices:

 execute ibeblock
 returns (info varchar(1000))
 as
 begin
   i = 0;
   for select i.rdb$index_name, i.rdb$relation_name, i.rdb$unique_flag,
              i.rdb$index_inactive, i.rdb$index_type
       from rdb$indices i
       left join rdb$relation_constraints rc on (i.rdb$index_name = rc.rdb$index_name)
       where (i.rdb$system_flag is null) and (rc.rdb$index_name is null)
       into :IdxName, :IdxRelName, :IdxUnique, :IdxInactive, :IdxType
   do
   begin
     sFields = ´´;
     for select rdb$field_name from rdb$index_segments
         where rdb$index_name = :IdxName
         order by rdb$field_position
         into :ifields
     do
     begin
       if (sFields <> ´´) then
         sFields = sFields || ', ';
       sFields = sFields || ibec_formatident(ibec_trim]ifields));
     end
     DropStmt[i] = 'drop index ' || ibec_formatident(ibec_trim(IdxName));
     CreateStmt[i] = 'create ' || ibec_iif(IdxUnique = 1, 'unique ', ´´) || ibec_iif(IdxType = 1, 'descending ', ´´) ||
                     ' index ' || ibec_formatident(ibec_trim(IdxName)) ||
                     ' on ' || ibec_formatident(ibec_trim(IdxRelName)) || ' (' || sFields || ')';
     i = i + 1;
   end
   i = 0;
   while (i <= ibec_high(DropStmt)) do
   begin
     s = DropStmt[i];
     info = s;
     suspend;
     ibec_progress(info);
     execute statement :s;
     commit;

     s = CreateStmt[i];
     info = s;
     suspend;
     ibec_progress(info);
     execute statement :s;
     commit;

     i = i + 1;
   end
 end 

See also:
Firebird for the Database Expert: Episode 1 - Indexes
Recreating Indices 2

back to top of page
<< Joining tables from different databases | IBEBlock | Recreating indices 2 >>