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

Sql Server-2005Non Clustered-Index

Sql Server-2005 Problem Overview


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?

Sql Server-2005 Solutions


Solution 1 - Sql Server-2005

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

Solution 2 - Sql Server-2005

Try this:

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser532104View Question on Stackoverflow
Solution 1 - Sql Server-2005AdaTheDevView Answer on Stackoverflow
Solution 2 - Sql Server-2005marc_sView Answer on Stackoverflow