Make changes to SQL Server stored procedures with batch editing

The database system that I maintain makes extensive use of stored procedures. Most of the common tasks performed...

with the database are handled exclusively by 2000 and 2005 stored procedures through SQL Server stored procedures. It's often possible to make global changes to the way the system behaves by simply editing one stored procedure in SQL Server. However, sometimes you need to change a whole slew of stored procedures all at once -- which is the kind of surgery you want to perform as delicately as possible. This, of course, assumes you're not using Visual Studio or some other IDE to perform this kind of work, which takes a lot of the drudgery out of it.

Here is my basic approach to making changes to a group of stored procedures in SQL Server:

1. Generate a SQL script that creates all of the relevant stored procedures in the database. Make sure the script performs any needed DROP FUNCTION commands as well, so any existing copies of the stored procedures are removed. The quick-and-dirty way to do this is to just use the Generate SQL Script context menu option for a database in SQL Server 2000 Enterprise Manager. In SQL Server 2005, it's the Generate Scripts option in the Tasks context menu option for a database.

   Note that the SQL Server 2005 Script Wizard looks very different from older versions, and if you're running SQL Server 2005 SP1, there is a small omission that may make repeated operations of this kind problematic. Under "Script Behavior," the only options are "Generate DROP statements only" and "Generate CREATE statements only." In other words, there's no option to generate DROP and CREATE

   Learn more about SQL Server stored procedures: Examples of SQL Server stored procedures and parameters Top 10 SQL Server development tips of 2007 Stored procedures in SQL Server: A dozen must-have tips

   statements in the same script, so that existing stored procedures with the same names can be preemptively dropped. A lot of people complained about this and, to the best of my knowledge, it was fixed in SQL Server 2005 SP2. If the inflexibility of the Script Wizard in SQL Server 2005 drives you crazy, you're not alone. Bill Graziano has created a third-party mini-tool named Scriptio to address some of these issues. It works with SQL Server 2000 as well.
2. Make a backup of this new script. This is very important. Do not work on the original file.
3. Open the script in your favorite text editor. The more complicated it is to do the search-and-replace work, the more probable it is you'll want to use an editor that has regular expressions. Even something as simple as Notepad2 will do the job most of the time. I strongly recommend an editor for your stored procedures that performs T-SQL syntax highlighting. Then, if you make any syntactical mistakes, they will be flagged before you attempt to re-import the script.
4. Merge back in the new, edited script and pay attention to any errors in the stored procedure function. If something fails, you can always use the backup script you made to restore the procedures to their original state. I'm leaving out any instructions regarding things like database versioning since those could vary immensely from site to site.

ABOUT THE AUTHOR
Serdar Yegulalphas been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.