A Tech Tip was recently published on how to Programmatically detect composite indexes. To use that Tech Tip you must visually analzye the results in a list box. Here is a snippet of code that can be used to quickly detect if you have compsite indexes in your database, returning the number of composite indexes programmatically:

C_LONGINT($0;$NumberOfCompositeIndexes_l) Begin SQL    SELECT COUNT(COLUMN_POSITION)    FROM _USER_IND_COLUMNS    WHERE COLUMN_POSITION = 2    INTO :$NumberOfCompositeIndexes_l; End SQL $0:=$NumberOfCompositeIndexes_l

If the above code is saved as a method named UTIL_GetNumberOfCompIndexes you can use it like this:

C_TEXT($msg_t) C_LONGINT($NumberOfCompositeIndexes_l) $NumberOfCompositeIndexes_l:=UTIL_GetNumberOfCompIndexes $msg_t:="You have "+String($NumberOfCompositeIndexes_l)+" Composite Indexes in your structure." ALERT($msg_t)

Each indexed field in the database is defined in the _USER_IND_COLUMNS system table and has unique a INDEX_ID; with the exception of composite indexes which have multiple fields defined for a single index. Composite indexes share the same INDEX_ID but have other identifying information within this system table's other fields.

Within the _USER_IND_COLUMNS system table there is a field named COLUMN_POSITION. This field defines the position of the field within a composite index. Remember, the order fields are added to a composite index matters; more info on why the order matters can be found here.

Based on this principal, if a composite index exists in the database, you should be able to find records in the _USER_IND_COLUMNS system table with a COLUMN_POSITION greater than 1.

The approach discussed in this tech tip uses the _USER_IND_COLUMNS system table and checks how many rows are found with a COLUMN_POSITION equal to 2. In this way, we only count each composite index once. The method returns the total number of composite indexes found.