How do I check if a nonclustered index exists in SQL Server 2005

Question

I have the following:

CREATE NONCLUSTERED INDEX [MyTableIndex]
ON [dbo].[tablename] ([tablename_ID],[tablename_Field1])
INCLUDE ([Tablename_Field2],[Tablename_Field3])

I want to create an if statement to check if this exists. How do I do this?

Possible duplicate of http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table — Michael Freidgeim, May 21 '13 at 01:38

score 207 · Accepted Answer · answered May 03 '11 at 14:35

207

IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'MyTableIndex' AND object_id = OBJECT_ID('tablename'))
    BEGIN
        -- Index with this name, on this table does NOT exist
    END

answered May 03 '11 at 14:35

AdaTheDev

142,592
28
206
200

2

+1 This is working nice. Should or not be concerned about the table schema? – gotqn Apr 10 '13 at 12:29
11

@gotqn - to take schema into account, you can just include it as a two part name in the OBJECT_ID call e.g. OBJECT_ID('myschema.tablename') – AdaTheDev Apr 10 '13 at 12:36

score 19 · Answer 2 · answered May 03 '11 at 14:35

19

Try this:

IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'MyTableIndex')
   -- put your CREATE INDEX statement here

answered May 03 '11 at 14:35

marc_s

732,580
175
1,330
1,459

11

I advise also checking object_id (as per AdaTheDev's answer) as index names aren't unique. – mcNux Jun 02 '15 at 14:10

How do I check if a nonclustered index exists in SQL Server 2005

2 Answers2