check if a non-clustered index exists on a table column

Question

In SQL Server 2000, how do I check if a non-clustered index exists on a single column of table?

Answer 1

Check out the "sysindexes" view... I'm on a mac now so I can't give you the exact, tested query..

SELECT * FROM sysindexes

Answer 2

check the "type" column (for value<>1, that means not clustered) of sysindexes table, here object_id column contains name of the table.

Answer 3

You look it up in sysindexes and sysindexkeys. You can also use sp_help to explain a table, including all indexes.

select k.*, x.name
from  sysindexes x 
join sysindexkeys k on k.id = x.id
join syscolumns c on c.id = x.id and k.colid=c.colid
where x.id = object_id('yourtable')
and c.name='yourcolumn'
and x.indid > 1

You can tell from the k.keyno column the position of key in the index, if is not 1 then the column is probably SARGable only if combined with other columns that are ahead of it in the index key order.