Home > IT Answers > Database > Stored Procedure for dropping indexes
Dangagne
30 pts.
 Stored Procedure for dropping indexes
DROP, Indexes, Restore, Stored Procedures
I would like a stored procedure that will take as input the name of a table. Whatthe procedure would do is drop all indexes on that table excpet any index that ends with U1 or P1, and save the information on the ones it drops. Then I would like another strored procedure that takes a table name as input and puts back all the indexes that were dropped. We need to load 1 to 3 million rows a night and we manually drop all the indexes and put them back after the load. Is this something that can be written as a stored procedure?

Software/Hardware used:
ASKED: February 28, 2008  4:04 PM
UPDATED: March 3, 2008  12:46 PM
RELATED QUESTIONS
Answer Wiki:
What database platform are you using? While this can be done, it is more efficient and much safer to hard code the list of indexes which need to be dropped into a stored procedure, and the same with the create index commands. It requires a little more maintenance when ever indexes are added to the tables, but the end result will be much better.
Last Wiki Answer Submitted:  February 29, 2008  1:41 am  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

I am using Microsoft SQL Server 2000, soon to be upgraded to SQL Server 2005

Dangagne
 30 pts.
 

I don’t even know where the SQL Text is stored for each index so I can retrieve it before actually deleting teh index. When I go into SQL Enterprise and Manage Indexes, I can view thetext, but I have no idea where and how it is stored.

Dangagne
 30 pts.