I am still switching over to ElevateDB from DBISAM.

One of the things I did in DBISAM was to use DeleteAllIndexes inside my
Delphi code.

For example:-
  MyDataModule.MyTable.DeleteAllIndexes;

That deleted all the indexes off the table MyTable.

Unfortunately DeleteAllIndexes is not available in ElevateDB.

I have searched the documentation in vain for a replacement. I did find
in the Discussion newsgroup the Subject "Delete all indexes for all tables".

I guess in SQL a statement might be:-
  SELECT TableName, Name FROM Information.Indexes
  WHERE TableName = MyTable;

However this might return multiple rows. Also it can't be used in the
Delphi code?

What is the recommended replacement for DeleteAllIndexes?

Regards,
  Peter Evans

Peter

>I guess in SQL a statement might be:-
> SELECT TableName, Name FROM Information.Indexes
> WHERE TableName = MyTable;

You're almost there with that bit of SQL.

procedure ZapTableIndices(Sesh: TEDBSession; DB: TEDBDatabase; const Table: string);
var
getter: TEDBQuery;
zapper: TEDBQuery;
begin
getter := TEDBQuery.Create(nil);
getter.SessionName := Sesh.SessionName;
getter.DatabaseName := DB.DatabaseName;
getter.SQL.Text := 'SELECT Name FROM Information.Indexes WHERE TableName = ' + QuotedStr(Table);
getter.ExecSQL;
if getter.RecordCount > 0 then begin
 zapper := TEDBQuery.Create(nil);
 zapper.SessionName := Sesh.SessionName;
 zapper.DatabaseName := DB.DatabaseName;
 getter.First;
 while not getter.Eof do begin
  zapper.close;
  zapper.SQL.Text := 'DROP INDEX "' + getter.FieldByName('Name').AsString + '" FROM "' + Table + '"';
  zapper.ExecSQL;
  getter.Next;
 end;
 zapper.Close;
 zapper.Free;
end;
getter.close;
getter.Free;
end;

Untested though.

Roy Lambert [Team Elevate]

Peter,

> However this might return multiple rows. Also it can't be used in the
> Delphi code?

You can use a script to do that. Just query your information database
and loop through the records.

Use something like that: (completely untested)

SCRIPT
BEGIN

DECLARE Result CURSOR WITH RETURN FOR Stmt;
DECLARE FIndexName VARCHAR;
DECLARE FTableName VARCHAR;

PREPARE Stmt FROM
  'SELECT Tablename, Name FROM Information.Indexes where type =
''Index'' order by Tablename';
OPEN Result;

FETCH FIRST FROM Result('Tablename','Name') INTO FTable, FIndexName;
WHILE NOT EOF(Result) DO

  Execute Immediate 'DROP INDEX ? FROM ?' using FIndexName, FTableName;

  FETCH NEXT FROM Result('Tablename','Name') INTO FTable, FIndexName;

END WHILE;

END

Uli

Roy, Uli,
Thanks. I have coded a routine. When my application compiles, in a week
or so (sigh), I will make a further reply.

Regards,
  Peter Evans

Peter


If you want to make it more DBISAMish you can always subclass the table.

Roy Lambert

Roy

sorry, didn't see your post.

Uli

Uli

>sorry, didn't see your post.

Never apologise for adding to a discussion. It always adds something. In this case I used Delphi / SQL and you used a script. Its good to have a choice.

Roy