SQL Server Performance

indexes

Discussion in 'General Developer Questions' started by bfarr23, Sep 4, 2003.

  1. bfarr23 New Member

    does anyone have a script to drop all indexes then add them back?
  2. ykchakri New Member

    DBCC DBREINDEX will just do this. You don't have to write another script.
  3. bfarr23 New Member

    i want to drop all indexes, pump ALOT of data in, add all the indexes back.

    dbcc dbreindex is no good for this situation.
  4. ykchakri New Member

    For the create index part you can make SQL Server generate a script for you, by clicking on the Generate SQL script option under EM. You have to modify the generated script file to remove the create table commands from it. You may also modify the same file to create a drop index script by replacing the 'create index' commands with 'drop index' and some additional modifications.
  5. bfarr23 New Member

    I was looking for a better solution that the "Generate SQL Script".

    someone must have code for this.
  6. Argyle New Member

  7. bfarr23 New Member

    can someone put the complete code in here for a drop all indexes, then create all indexes?
  8. ykchakri New Member

  9. bfarr23 New Member

    ya I have that code. Thx.

    May have to draft up my own I guess. No one seems to have the complete code created for what I want.


  10. bfarr23 New Member

    Well I think I have it.

    This code will script out the drop index for all non-clustered indexs(excluding any unique primary key indexes).

    DROP INDEX...

    select 'drop index ' + object_name(ind.id) + '.' + ind.name
    from sysindexes ind, sysobjects obj
    where ind.id = obj.id
    and obj.xtype = 'U'
    and obj.type <> 'K'
    and ind.name not like '_WA%'
    and ind.name not like coalesce((select constraint_name from information_schema.table_constraints where constraint_name = ind.name), '')
    and ind.indid > 1 and ind.indid < 255
    order by obj.name


    CREATE INDEX...
    This above result set will match the "Generate SQL Script" for "Script Indexes" only. (remove the clustered indexes that are scripped out and you have a match.)





Share This Page