QUICK & DIRTY: Table name change for all stored procedures

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

Generatescrpts

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.

scripts1

Step 2: Choose Stored Procedures all or specific ones

scripts2

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.

scripts3scripts4

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.

Leave a Reply

Your email address will not be published. Required fields are marked *