Recently, I was tasked to find all stored procedures in a database that use a particular table and change them to utilize another table. I needed to get this done quickly so I decided to think simple. Since it was a quick and dirty answer I figured I would share, it’s not rocket science, but useful none the less.
The quick answer that I came up with is to script out all of the stored procedures into a single query window. This can be done easily through the GUI. Once that is complete, I can easily do a “Find & Replace” on the table name and we’re done!
Let me show you how!
Step 1: Script out all stored procedures for a database
Right Click on Database > Choose Tasks> Choose Generate Scripts
If you have not used Generate Scripts before it has a great wizard to walk you through it. You can create scripts for the entire database or just certain objects. You can even narrow it down to a single stored procedure. In this case I wanted all stored procedures.
Step 2: Choose Stored Procedures all or specific ones
Step 3:
Then I went to the ADVANCE Options and set Continue on Error to TRUE so it will skip any issues that might surface. If there are issues, I will address them at a later time. I also chose to create the scripts to a new query window. By doing this, it’ll be very easy to do a find & replace on the table names.
Step 4: Once the new query window was populated I did a simple find and replace of the table name I wanted to change. I also changed the word CREATE PROCEDURE to ALTER PROCEDURE for all of the procedures so that I would not need to drop and replace.
Step 5: Back up your database!!! Another option is to simply keep a copy of the script before any modifications had been made. In the event of an error, I can just rerun the original script to put things back to what they were.
Step 6: Run Script
In this case, 365 stored procedures were updated and I had the process completed in about 15 minutes. Not too shabby.
*Remember it’s best to always do this in test first.
