Drop all indexes for a given table

There was a question in the german Xbase++ Forum how to drop all indexes for a given table in ADS if the table is part of a data dictionary (just deleting the index file won’t work in that case). The easiest way is to use SQL:

DECLARE @tablename STRING;
DECLARE @indexes CURSOR as SELECT Name FROM system.indexes WHERE Parent LIKE @tablename; 
@tablename='test';
OPEN @indexes;
WHILE FETCH @indexes DO
    EXECUTE IMMEDIATE 'DROP INDEX ['+trim(@tablename)+'].['+trim(@indexes.Name)+']';
END WHILE;
CLOSE @indexes;

If an index can’t be dropped (e.g. because it’s being used for a referential integrity rule), the whole script will fail with an exception. This might leave the index in a intermediate state (some indexes are droppped, others not). You can put TRY/CATCH clauses around the DROP command just to fail with un-dropable (is this the right english term?) indexes but still drop all others. And if you need this more than once, you should consider implementing it as a stored procedure:

CREATE PROCEDURE DropAllIndexes(
  tablename CICHAR(255), 
  indexname MEMO OUTPUT, 
  result logical OUTPUT, 
  errormessage MEMO OUTPUT)
BEGIN

DECLARE @tablename STRING;
DECLARE @indexname STRING;
DECLARE @indexes CURSOR as SELECT Name FROM system.indexes WHERE Parent LIKE @tablename; 
@tablename=_tablename;
OPEN @indexes;
WHILE FETCH @indexes DO
  TRY
    @indexname='['+trim(@tablename)+'].['+trim(@indexes.Name)+']';
    EXECUTE IMMEDIATE 'DROP INDEX '+@indexname;
	INSERT INTO __output VALUES (@indexname, true, '');
  CATCH ALL
	INSERT INTO __output VALUES (@indexname, false, __errtext);
  END TRY;
END WHILE;
CLOSE @indexes;

END;

Usage:

EXECUTE PROCEDURE DropAllIndexes('test');