Home Forums SQL Server 2008 T-SQL (SS2K8) IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX RE: IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX

  • This is how I would code it. Also note that I am using sys.indexes, not sysindexes.

    IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')

    DROP INDEX indexname ON SCHEMA.tablename;

    GO

    CREATE INDEX indexname ON SCHEMA.tablename(columnlist);

    GO

    Edit: Added batch separators just to be safe. Can't remember if the CREATE INDEX has to be first in a batch and quick look in BOL didn't exactly answer the question. Will need to dig a bit more to be sure.