How do I check if a nonclustered index exists in SQL Server 2005
Sql Server-2005Non Clustered-IndexSql 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