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

  • August 13, 2012 at 2:36 pm

    #1525281

    Lynn Pettis (8/13/2012)

    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;

    CREATE INDEX indexname ON SCHEMA.tablename(columnlist);

    Why not simply:

    Create Index IndexName on schema.tablename (columnlist)

    with (drop_existing = on);

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon