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 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 ...
   
   To continue reading for free, register below or login

   Requires Membership to View

   To gain access to this and all member only content, please provide the following information:

   Email Address:
   
   
   

   By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
   
   TechTarget cares about your privacy. Read our Privacy Policy
   To read more you must become a member of SearchSQLServer.com

   As an existing member of the TechTarget network please activate your SearchSQLServer.com account 

   By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
   
   TechTarget cares about your privacy. Read our Privacy Policy
   
   
   

   Digg This!     StumbleUpon     Del.icio.us

   
   
   
   

   RELATED CONTENT SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Database Development Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Working with IntelliSense in SQL Server 2008 Management Studio Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility

   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

   
   
   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. 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.