Check if non-clustered index exists in table column

Question

In SQL Server 2000, how can I check if a non-clustered index exists in one column of a table?

Answer 1

You are looking at sysindexes and sysindexkeys . You can also use sp_help to explain the 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 specify the key position in the index from the k.keyno column, if not 1, then the column is probably SARGable only if it is combined with other columns that are ahead of it in index order.

Answer 2

"sysindexes"... mac, , .

SELECT * FROM sysindexes

Answer 3

check the "type" column (for the value <> 1, which means not clustered) of the sys.indexes table type_desc indicates CLUSTERED, NONCLUSTERED or HEAP