indexes

does anyone have a script to drop all indexes then add them back?

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

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

dbcc dbreindex is no good for this situation.

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.

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

someone must have code for this.

See:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1413

and:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=652

/Argyle

can someone put the complete code in here for a drop all indexes, then create all indexes?

I've found this for dropping all indexes of a specified table.

http://www.databasejournal.com/img/sp_dropindex.sql

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.

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.)