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 Yegulalp has been writing about Windows and related technologies for more than 10 years and is a frequent contributor to various sections of TechTarget as well as other publications. He hosts the Web site Windows Insight, where he posts regularly about Windows and has an ongoing feature guide to Windows Vista for emigrants from Windows XP. Copyright 2008 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT SQL Server stored procedures Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security SQL and SQL Server Tutorial and Reference Guide Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands Stored procedure to monitor long-running jobs in SQL Server 2000 Using BULK INSERT to insert rows from SQL Server dataset to table SQL Server performance and tuning Virtual database storage for SQL Server: Friend or foe? Tutorial: SQL Server 2005 Analysis Services Tutorial: Migrating to SANs from local SQL Server disk storage SQL Server memory configurations for procedure cache and buffer cache Using the OUTPUT clause for practical SQL Server applications Check SQL Server database and log file size with this stored procedure SQL Server PerfMon counters for access methods and buffer manager Find size of SQL Server tables and other objects with stored procedure Monitor SQL Server disk I/O with PerfMon counters SQL Server tempdb best practices increase performance Stored Procedures Check SQL Server database and log file size with this stored procedure Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Stored procedure to monitor long-running jobs in SQL Server 2000 Stored procedure to find fragmented indexes in SQL Server Use table-valued parameters for SPs in SQL Server 2008 Examples of SQL Server stored procedures and parameters Top 10 SQL Server development questions FAQ: SQL Server stored procedure how-tos Q&A: Writing stored procedures in SQL Server 2005 RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.