Script to drop all indexes on a table ...

[jeffmoore64]

Hi,
Anyone have a neat script to drop all the indexes on a table except an index that starts with "PK"?
I need to insert about 55-60 million rows into a table that has a bunch of indexes on it. Last time we did this with the indexes in place to took about 40 hours. I'm hoping to speed this up by dropping all the indexes except the PK and then create the indexes I dropped after we load the table.
Sound like a reasonable plan?
TIA,
Jeff

 

[gmmastros]

Take a look here: thread183-1151951

It doesn't show you how to drop the indexes, but it does show which indexes should be dropped and which ones shouldn't.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

You can get the script yourself by scripting the table with drop and recreate and include all the idexes. That wil give you the code to drop all the indexes and the code to recreate them. Just make sure you don't run the drop and recreate table parts or the PK!

If you decide to do this make sure you schedule it during off-peak hours as any queries on the database at the time this is running will be dirt slow. it might be faster, but especially with tables this large, I'd test this process on development before I tried it on poroduction.

You might also consider running the insert in batches. There is an FAQ on doing that to increase performance.

Questions about posting. See faq183-874