CODEWITHSUNDEEP
sql-serverindexing
Dan
Dan

Reputation: 941

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

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

Upvotes: 3

Views: 8196

Answers (3)

Alex_L
Alex_L

Reputation: 2654

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

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 5

Dave Markle
Dave Markle

Reputation: 97791

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

SELECT * FROM sysindexes

Upvotes: 1

Related Questions