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:

1> sp_helpindex authors
2> go
Object has the following indexes
 
 index_name index_keys          index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local 
 ---------- ------------------- ----------------- ----------------------- ---------------- -------------------- ------------------- ------------
 auidind     au_id              clustered, unique                       0                0                    0 Feb  3 2013  2:27PM Global Index
 aunmind     au_lname, au_fname nonclustered                            0                0                    0 Feb  3 2013  2:27PM Global Index

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:

1> sp_help_params sysindexes
2> go
 Parameter_name Type        Length Prec Scale Param_order Mode   
 -------------- ----------- ------ ---- ----- ----------- -------
 name           longsysname    255 NULL  NULL           1 unknown
 id             int              4 NULL  NULL           2 unknown
 indid          smallint         2 NULL  NULL           3 unknown
 doampg         int              4 NULL  NULL           4 unknown
 ioampg         int              4 NULL  NULL           5 unknown
 oampgtrips     int              4 NULL  NULL           6 unknown
 status3        smallint         2 NULL  NULL           7 unknown
 status2        smallint         2 NULL  NULL           8 unknown
 ipgtrips       int              4 NULL  NULL           9 unknown
 first          int              4 NULL  NULL          10 unknown
 root           int              4 NULL  NULL          11 unknown
 distribution   int              4 NULL  NULL          12 unknown
 usagecnt       smallint         2 NULL  NULL          13 unknown
 segment        smallint         2 NULL  NULL          14 unknown
 status         smallint         2 NULL  NULL          15 unknown
 maxrowsperpage smallint         2 NULL  NULL          16 unknown
 minlen         smallint         2 NULL  NULL          17 unknown
 maxlen         smallint         2 NULL  NULL          18 unknown
 maxirow        smallint         2 NULL  NULL          19 unknown
 keycnt         smallint         2 NULL  NULL          20 unknown
 keys1          varbinary      255 NULL  NULL          21 unknown
 keys2          varbinary      255 NULL  NULL          22 unknown
 soid           tinyint          1 NULL  NULL          23 unknown
 csid           tinyint          1 NULL  NULL          24 unknown
 base_partition int              4 NULL  NULL          25 unknown
 fill_factor    smallint         2 NULL  NULL          26 unknown
 res_page_gap   smallint         2 NULL  NULL          27 unknown
 exp_rowsize    smallint         2 NULL  NULL          28 unknown
 keys3          varbinary      255 NULL  NULL          29 unknown
 identitygap    int              4 NULL  NULL          30 unknown
 crdate         datetime         8 NULL  NULL          31 unknown
 partitiontype  smallint         2 NULL  NULL          32 unknown
 conditionid    int              4 NULL  NULL          33 unknown

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

1> select name, id, object_name=object_name(id), indid, keycnt from sysindexes where id=object_id('authors')
2> go
 name     id          object_name indid  keycnt
 -------- ----------- ----------- ------ ------
 auidind    576002052 authors          1      1
 aunmind    576002052 authors          2      3

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:

1> sybsystemprocs..sp_helptext sp_helpindex
2> go
...

	/*
	**  First we'll figure out what the keys are.
	*/
	declare @i int
	declare @thiskey varchar(255)
	declare @sorder char(4)	
	declare @lastindid int

	select @keys = "", @i = 1

	set nocount on

	while @i <= 31
	begin
		select @thiskey = index_col(@objname, @indid, @i)

		if (@thiskey is NULL) 
		begin
			goto keysdone
		end

		if @i > 1
		begin
			select @keys = @keys + ", " 
		end

		select @keys = @keys + @thiskey

		/*
		** Get the sort order of the column using index_colorder()
		** This support is added for handling descending keys.
		*/
		select @sorder = index_colorder(@objname, @indid, @i)
		if (@sorder = "DESC")
			select @keys = @keys + " " + @sorder

		/*
		**  Increment @i so it will check for the next key.
		*/
		select @i = @i + 1

	end

Bingo! The answer is hidden in this line:

select @thiskey = index_col(@objname, @indid, @i)

Let’s check what the documentation says about index_col:

index_col

Description

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

Syntax

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

object_name
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.

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

key_#
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.

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

Examples

Example 1

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

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

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:

IF EXISTS (SELECT 1
           FROM master..spt_values
           WHERE type = 'P'
           AND   number BETWEEN 1 AND 31
           AND   index_col('authors'
                           , (SELECT indid
                              FROM sysindexes
                              WHERE id    = object_id('authors')
                              AND   name  = 'aunmind')
                           , number) = 'au_fname')
BEGIN
   PRINT 'INFO: aunmind index on authors table contains au_fname column.'
END
ELSE
BEGIN
   PRINT 'WARNING: au_fname column is not contained within aunmind on authors table.'
   EXECUTE ("DROP INDEX authors.aunmind")
   EXECUTE ("CREATE NONCLUSTERED INDEX aunmind ON authors (au_lname, au_fname)")
END

That’s it for today.