Title: Checking a table for an index / Creating an Index in a table
Question: I want to know if a table has a particular index, and if it doesn't I want to be able to add it
Answer:
My friend should be taking most of the credit for this...
He helped me create the raw code that has been refined to what you are about to see.
Behold
-----------
Checking for an index in a table is done with this function
function IndexExists(AnAlias,ATableName,AIndexName:String):Boolean;
var
ATable:TTable;
TempName:String;
begin
Result:=true;
try
ATable:=TTable.Create(Application);
With ATable do begin
DatabaseName := AnAlias;
TableName:=ATableName;
IndexName:= AIndexName;
try
Open;
if Active then Close;
except
if (Exception(ExceptObject) is EDatabaseError) then begin
TempName:=(Exception(ExceptObject) as EDatabaseError).Message;
if Pos('Index does not exist',TempName)0 then begin
Result:=false;
exit;
end;
Raise; // Some other error occurred so re-raise the exception
end;
end;// end except
end;
finally
if ATable.Active then ATable.Close;
ATable.Free;
end;
end; // end indexexists
ok, You can use the above function in to main ways
// Alias type
if IndexExists('AliasName','TableName','IndexName') then;
// Directory Type
if IndexExists('','C:\path\tablename','IndexName') then;
-----------
Creating an index in a table is done with this procedure
procedure CreateIndex(AnAlias,ATableName,AIndexName:String)
var
AQuery : TQuery;
begin
AQuery := TQuery.Create(application);
if (AnAlias[1] '') then
AQuery.Sql.Add('create index ' + AIndexName + ' on ":' + AnAlias + ':' + ATableName + '"')
else
AQuery.Sql.Add('create index ' + AIndexName + ' on "' + ATableName + '"');
AQuery.ExecSql;
AQuery.free;
end;
Again, The calling of the above can be something like this
// Alias type
if CreateIndex('AliasName','TableName','IndexName') then;
// Directory Type
if CreateIndex('','C:\path\tablename','IndexName') then;
I know... Someone out there would probably have something better, If you do, let us know.
Thanks
Shannon