Hi,

I am trying to write a script that will delete (DROP) all indexes for all tables.  I started with the bellow code but cannot move on.  Any idea how to that?


DECLARE sTableName  VARCHAR;
DECLARE sIndexName  VARCHAR;
DECLARE qryTables   CURSOR FOR sSQL1;
DECLARE qryIndexes  CURSOR FOR sSQL2;

PREPARE sSQL1 FROM 'SELECT Name FROM Information.Tables';

OPEN qryTables;

FETCH FIRST FROM qryTables('Name') INTO sTableName;

WHILE NOT EOF(qryTables) DO


     FETCH NEXT FROM qryTables('Name') INTO sTableName;
END WHILE;
CLOSE qryTables;

Regards,
Abdulaziz Jasser

Abdulaziz


There's a better table to use - Information.Indexes. It would go something like


DECLARE sTableName  VARCHAR;
DECLARE sIndexName  VARCHAR;
DECLARE qryTables   CURSOR FOR sSQL1;
DECLARE Dropping VARCHAR;

PREPARE sSQL1 FROM 'SELECT TableName, Name FROM Information.Indexes';

OPEN qryTables;

FETCH FIRST FROM qryTables('TableName',Name') INTO sTableName, sIndexName;

WHILE NOT EOF(qryTables) DO
     SET Dropping = 'DROP '+sIndexName+' FROM '+sTableName;
     EXECUTE IMMEDIATE Dropping;
     FETCH NEXT FROM qryTables('TableName',Name') INTO sTableName, sIndexName;;
END WHILE;
CLOSE qryTables;

totally untested

Roy Lambert [Team Elevate]

Roy,

Big thanks....
Regards,
Abdulaziz Jasser