How to find the names of columns in an Index on a table in Sybase ASE?

Ever wondered how sp_helpindex can get the column names in an index created on a Sybase table when the same information is not available handy via the system tables. Well, looking into the system stored procedure sp_helptext throws some light on how this can be achieved and what is the master key to find the column names of an index.

To look at the system stored procedure we will get the procedures text via another stored procedure sp_helptext. The syntax to get a stored procedures text is as follows -

sp_helptext <<procedure name>>

So, for our case it will be -

sp_helptext sp_helpindex

Now if we look at the system stored procedure sp_helpindex we will find that there is a special Sybase function index_col which is being used within a while loop to extract column names for each index existing on the table. Therefore to find the column names of an index in a script we can use index_col function. To demonstrate this we will create an example where we will try to find out column names in an index on a table in Sybase ASE and see if index_col function will really help us.

To begin with the example, first we will create a table MyTab with the following create table command -

create table MyTab
(
   Id int,
   Name varchar(100),
   Dsc varchar(100),
  StartDate datetime,
  EndDate datetime
)
go

and then we will create a clustered index on this table using the create index command -

create clustered index MyTabC on MyTab(Id, StartDate)
go

As we can see in the above statement we have created a clustered index on MyTab table over the columns - Id and StartDate.
Just to counter check we will execute a query on sysindexes table to see if we have actually created a clustered index on the table -

select * from sysindexes where id = (select id from sysobjects where name = 'MyTab')
go

On executing the above statement we find that yes we have actually created a clustered index on our table with the index id (refer column indid in sysindexes table) of 1.
Now, just before creating the script to find index column names we will first have a look at the syntax of Sybase function index_col. The syntax for index_col function is -

index_col(Table_Name, Index_Id, key_index, user_id)

As we can see in the above statement -

Table_Name - is the table for whose index we are trying to find the column names,
Index_Id - is the id of the index as per sysindexes system table,
Key_Index - is the index of the field or column which consitituted the index and can vary upto keycnt column value in the sysindexes table,
User_Id - is the owner of the object. In case we do not specify the same then it will default to the current user.

Now, once everything is done and the syntax for index_col understood well, we will try to find out the names of columns in the index using a simple script shown below-

declare @keys integer
select @keys = keycnt from sysindexes where id = object_id("MyTab") and indid = 1
while @keys > 0
begin
select index_col("MyTab", 1, @keys)
select @keys = @keys - 1
end
go

As we can see in the above script we first tried to find out the total key count for the index whose index id is 1; what it actually means is that we will find the key count for the clustered index we have created already which will give us the value 2. We get the key count from the keycnt column in sysindexes table. Once we have the key count with us we simply looped through to find column names one at a time. The command -

select index_col("MyTab", 1, @keys)

on first iteration will give the column name - StartDate and on the next it will give the Id column name.

And finally that's all we required to find the column names in an index. Now as a next step we can refine the above script to form a string and return the result to us but thats an experimental program left for you all... Will be back with some more good stuff later. Till then have a great day ahead..