How can i determine if a index has been created

[sandy771]

I know the name of a particular index on a table but I need to check to see if it has been created - is there a query I can do that will check if an index has been created?

[CristianP]

Hi,

You will need to play a little with TUniMetaData or UniConnection1.CreateMetaData.
You can connect UniMetaData1 to a grid and see the columns.
You can use UniMetaData1.Restrictions or UniMetaData.Locate('TABLE_NAME;INDEX_NAME', ....)

Best Regards,
Cristian Peta

[CristianP]

Hi,

In dbExpress is TSQLConnection.GetIndexNames()
But do not see a similar function in UniDac and I have created my own:

Code: Select all

procedure GetIndexNames(uc: TUniConnection; TableName: String; SList: TStringList);
var
  meta: TDAMetaData;
  iPunct: Integer;
  OnlyTableName: String;
  DBName, Schema: String;
begin
  SList.CaseSensitive := False;
  SList.Clear;
  meta := uc.CreateMetaData;
  try
    meta.MetaDataKind := 'Indexes';

    //here I play a little with restriction for SQLite, PostgreSQL, SQL Server and MySQL
    //but this is specifically for my application and I deleted sources  
    //meta.Restrictions.Values['TABLE_CATALOG'] := DBName;
    //meta.Restrictions.Values['TABLE_SCHEMA'] := Schema;

    meta.Restrictions.Values['TABLE_NAME'] := TableName;
    meta.Open;
    meta.First;
    while not meta.Eof do begin
      SList.Add(meta.FieldByName('INDEX_NAME').AsString);
      meta.Next;
    end;
  finally
    meta.Free;
  end;
end;

Best Regards,
Cristian Peta

[DemetrionQ]

Hello.

To check index availability, use the TUniMetaData component. Working code example:

Code: Select all

  UniMetaData1.MetaDataKind:='Indexes';
  UniMetaData1.Restrictions.Values['TABLE_NAME'] := 'YourTableName';
  UniMetaData1.Restrictions.Values['INDEX_NAME'] := 'YourIndexName';
  UniMetaData1.Open;
  if not UniMetaData1.IsEmpty then begin
    // index exists;
  end;

[sandy771]

Thanks all.