How do I work out whereabouts in an index the column is in a multi column index?

Roy Lambert

Roy,

<< How do I work out whereabouts in an index the column is in a multi column
index? >>

Use the IndexColumns table:

http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=edb1sql&category=3&topic=64

The position of the column in the index is the natural order of the rows as
found in that table, so just count up to the desired column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

I thought you were going to say that.

In my query generator I want to figure out if there's an index that can be used, and if so grab the collation so I can alter the UI to let people know case-insensitive is/isn't going to be fast. I'm currently building this query

idxChk.SQL.Add('SELECT');
idxChk.SQL.Add('IF(X.Type = ' + QuotedStr('Text Index') + ',True,False) AS IsTextIndex,');
idxChk.SQL.Add('Collation AS idxCollation,');
idxChk.SQL.Add('T.Collation AS fldCollation');
idxChk.SQL.Add('FROM  Information.IndexColumns XC');
idxChk.SQL.Add('LEFT JOIN Information.Indexes X ON X.Name = XC.IndexName');
idxChk.SQL.Add('LEFT JOIN Information.TableColumns T ON T.TableName = :Tbl AND T.Name = :Fld');
idxChk.SQL.Add('WHERE');
idxChk.SQL.Add('XC.TableName = :Tbl');
idxChk.SQL.Add('AND');
idxChk.SQL.Add('ColumnName = :Fld');
idxChk.SQL.Add('AND');
idxChk.SQL.Add('X.TableName = :tbl');


What I want to do is be able to select just those indices that are either a text index or have the selected column at the start. Any suggestions?

Roy Lambert