SQL Server Index Scripting

views:

answers:

SQL Server Index Scripting

I'm using SQL Server 2008.

Is it possible to create a script to loop over all tables in a database generating a set of index drop scripts and create scripts separately?

What I have to do is drop all indexes on a set of databases to run a heavy data load process but then I want to re-enable all the indexes. I don't want to have to go through each table and script an index drop and then index create.

Yes. Someone's already done it so no need to write a script again... :-)

Drop All Indexes and Stats in one Script

Edit. Oops. To create...

Before you drop them... In SSMS, right-click, generate scripts, etc

gbn 2010-05-07 19:24:35

So how's he going to re-create them :)

dcp 2010-05-07 19:26:48

I ended up using this script to do the drops then I found an internal tool someone created to script out all the indexes thanks :).

Fouad Masoud 2010-05-14 16:35:23

+1 A:

I think what you want to do is here. You don't really need to drop the indexes, just disable them while your load is occurring. This script will allow you to disable the indexes for your load, then re-enable when done, and the beauty is it does it all in one shot for the whole database with a single command.

dcp 2010-05-07 19:28:16

That's an outstanding SP.

rwmnau 2010-05-07 19:56:51

The problem with disabling the index is you can't access the table anymore, if its clustered I think.

Fouad Masoud 2010-05-14 16:33:42

You could do it with SMO, take a look at SMO Script Index and FK's on a database

SQLMenace 2010-05-07 19:31:17

ansaurus

tags:

views:

answers:

SQL Server Index Scripting

related questions