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
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])
Parametersobject_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.
