Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 361
user532104 Avatar asked May 03 '11 14:05

user532104


People also ask

Where are non-clustered indexes stored?

If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

How many non-clustered Indexs are there in SQL Server 2005?

In SQL Server 2005 and earlier, a maximum of 249 non-clustered indexes could be created on a table but now in SQL Server 2008 that limit has been increased and now 999 non-clustered indexes can be created on a single table.


1 Answers

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 
like image 160
AdaTheDev Avatar answered Nov 28 '22 19:11

AdaTheDev