SQL Server – I would like a stored procedure that will take the name of a table as input.

Tags:
SQL Server
SQL Server performance
Stored Procedures
I would like a stored procedure that will take the name of a table as input. What the procedure would do is drop all indexes on that table except any index that ends with U1 or P1, and save the information on the ones it drops. Then I would like another stored 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?
1

Answer Wiki

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

I’ll post the same answer here as when Dangagne asked the question on the forum back on Feb 28.

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:  

 
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.

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.

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

Following

Share this item with your network: