SQL Server 2008 Check if an index exists

Question

Possible Duplicate:
List of all index & index columns in SQL Server DB

I would like to know if there is a way to verify if an index exists in a SQL Server database for a specific table based on the columns name:

Let's say I run the following script:

  CREATE NONCLUSTERED INDEX [MyIndexName]
  ON [dbo].[MyTable] ([CustomerId])
  INCLUDE ([Id],[ModificationDate],[ProductId])
  GO

Now I would like to check if the index exists based on the table name and columns (and the columns in the include clause), not the actual index name.

(SQL Server 2008 R2)

Thanks

Not sure how you want to verify. Perhaps you can use [something like this](http://sqlfiddle.com/#!3/7b82b/1/0) — Mikael Eriksson, Nov 13 '12 at 14:29
It's actually duplicate of different question-http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table — Michael Freidgeim, May 21 '13 at 01:49
@MichaelFreidgeim - Your linked question provides an answer only if you already know the name of the index. The op specifically requested an answer where the table and column name are known, but the index name (if it exists) is unknown. — Zarepheth, Jun 14 '13 at 17:36

score 35 · Answer 1 · answered Nov 13 '12 at 14:17

35

Try this query:

if exists(
           SELECT 1 
           FROM sys.indexes 
           WHERE name = 'INDEX' 
           AND object_id = OBJECT_ID('TABLENAME')
          )
 begin
 ....
 end

answered Nov 13 '12 at 14:17

Robert

25,425
8
67
81

score 0 · Answer 2 · answered Nov 13 '12 at 14:42

The information is available in the catalog metadata views, something along the lines of:

select ...
from sys.indexes i
join sys.index_columns ic on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c
    on ic.object_id = c.object_id
    and ic.index_column_id = c.column_id
where i.object_id = object_id('MyTable')
and (c.name = 'CustomerId' and ic.key_ordinal = 1 and ic.is_descending_key = 0)
or (ic.is_included_column = 1 and c.name in ('Id', 'ModificationDate', 'ProductId'));

This is not producing a yes/no answer but is showing you the indexes that could be already covering the one you're planning. You need to account for both key position, key direction and included columns, with covering overlaps (an index on (K1, K2) covers (K1), and an index which include (C1, C2, C3) covers (C1, C3). For a real production system you would need to consider things like implicitly included columns (eg. the clustered index keys), index filter expressions and differences in index data space (ie. partitioninig)

SQL Server 2008 Check if an index exists

2 Answers2