Posted by Erland Sommarskog on 12/07/06 22:20

frien (001frien@gmail.com) writes:
> can anybody plz....help in a stored procedure..............i basically
> need a simple stored procedure to see if a index exists in the table if
> it it doesnot exist i need to create a index for the same table

IF indexproperty(object_id('mytable'), 'myindex', 'IsClustered') IS NULL
CREATE INDEX myindex ON mytable(mycol)

The key here is that indexproperty returns NULL is the index does
not exist.

This look-up is by name. A more ambitious check would look at the existing
indexes, their columns and other properties.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]