Sybase : How to check programmatically if index contains particular column?

So you’d like to check if a certain column is included in an index created on a table, that’s easy:

unless you want to do that in a programmatic way, because parsing sp_helpindex output is out of question and you’d like to perform some actions depending on the result (eg. drop & recreate the index with the missing column).

The first and most obvious idea was to check if this information is in the sysindexes table, so, let’s check:

unfortunately, there is no name of the column included in this table:

Okay, so if the information is not there, how does sp_helpindex know what to display? Let’s check by looking at the sp_helpindex source code:

Bingo! The answer is hidden in this line:

Let’s check what the documentation says about index_col:



Returns the name of the indexed column in the specified table or view.


index_col (object_name, index_id, key_# [, user_id])

is the name of a table or view. The name can be fully qualified (that is, it can include the database and owner name). It must be enclosed in quotes.

is the number of object_name’s index. This number is the same as the value of sysindexes.indid.

is a key in the index. This value is between 1 and sysindexes.keycnt for a clustered index and between 1 and sysindexes.keycnt+1 for a nonclustered index.

is the owner of object_name. If you do not specify user_id, it defaults to the caller’s user ID.


Example 1

Finds the names of the keys in the clustered index on table t4:

Pretty neat! But for my use case, not good enough, I don’t want to run a while loop every time I’d like to check that, so how to get rid of the loop?

Let’s make use of set based approach. I’m going to project a list of 31 integers from 1 to 31 (31 is the maximum amount of columns that a composite index can contain) that are available and waiting for you in master..spt_values table:

That’s it for today.