Stored Procedure for dropping indexes

30 pts.
Tags:
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dangagne
    I am using Microsoft SQL Server 2000, soon to be upgraded to SQL Server 2005
    30 pointsBadges:
    report
  • Dangagne
    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.
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following