Script drop all indexes for all tables

  • Is there a script available to drop all indexes, pks, fks for all tables ? I've seen a few for tables only, nothing for all tables.

    ¤ §unshine ¤

  • try this script. Combine it with sp_msforeachtable to disable or enable indexes, depending on @switch

    Note: this stored procedure will only disable nonclustered indexes or indexes which are added for performance considerations.

    constraint and primarykey indexes are skipped.

    create procedure usp_toggle_index(@objectname sysname, @switch bit = 0, @debug bit = 0, @verbose bit = 1) as

    begin

    declare @SQLCmd nvarchar(512)

    declare @action nvarchar(16)

    declare @counter int

    set @counter = 0

    if @switch = 0

    set @action = ' disable;'

    else

    set @action = ' rebuild;'

    declare c_toggle_index cursor for

    select'alter index ' + QUOTENAME(name) + ' on ' + QUOTENAME(@objectname) + @action

    fromsys.indexes

    wheretype_desc = 'nonclustered'

    andis_unique = 0

    andis_primary_key = 0

    andis_unique_constraint = 0

    andis_disabled = @switch

    andobject_id = object_id(@objectname)

    openc_toggle_index

    fetchnext

    fromc_toggle_index

    into@SQLCmd

    while @@fetch_status = 0

    begin

    set @counter = @counter + 1

    if @debug = 0

    exec (@SQLCmd)

    else

    print @SQLCmd

    fetch next

    from c_toggle_index

    into @SQLCmd

    end

    close c_toggle_index

    deallocate c_toggle_index

    if @verbose = 1

    print case @switch

    when 1 then '- rebuilding ' + cast(@Counter as varchar) + ' indexes for table ' + @objectname

    else '- disabling ' + cast(@Counter as varchar) + ' indexes for table ' + @objectname

    end

    end

    go

    To disable indexes for all tables: exec sp_msforeachdb 'exec usp_toggle_index ''?'',0'

    Make sure you're in the right database. execute with @debug=1 to see the actual statements, insted of executing these.

    Wilfred
    The best things in life are the simple things

  • I'm actually looking for something to drop/remove all indexes. But this is great for other projects.

    ¤ §unshine ¤

  • Here is one....

    SELECT

    'DROP INDEX ' +

    QUOTENAME(USER_NAME(o.uid)) +

    '.' +

    QUOTENAME(o.name) +

    '.' +

    QUOTENAME(i.name)

    FROM sysobjects o

    JOIN sysindexes i ON

    i.id = o.id

    WHERE i.indid BETWEEN 1 AND 254 AND

    INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND

    INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND

    OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

    ¤ §unshine ¤

  • are you sure you want to drop all indexes?

    you have the scripts to re create them?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thank you!

    ¤ §unshine ¤

  • Warning DROP INDEX for a PK does *not* work! you must DROP the CONSTRAINT!


    * Noel

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply