Find and Replace Keywords in Stored Procedures

A tedious task that many DBAs perform is modifying stored procedures when an object name changes. For example, a project might require that stored procedures no longer reference the Employee table but the vEmployee view instead. In SQL Server 2000, stored procedures are stored in the syscomments system table, which is difficult to work with. Fortunately, in SQL Server 2005, stored procedures are stored in the sys.sql_modules system table.

The sys.sql_modules catalog view exists in each database. It has a row for every object, including stored procedures. Each stored procedure’s definition is stored in the definition column, which is of the nvarchar(max) data type. I created a stored procedure, FindAnd Replace.sql, that uses sys.sql_modules to identify stored procedures that contain a given keyword in their definitions. For each stored procedure that contains the keyword, FindAndReplace.sql outputs an alter script and a backup script. The alter script, if run, will delete the existing definition and create a new one that contains the replacement keyword. The backup script stores a copy of the unmodified definition.

FindAndReplace.sql takes three parameters:

For example, the code in Listing 1 uses FindAnd Replace.sql to search for [Employee] in the definition column of sys.sql_modules in the AdventureWorks database. For each stored procedure that contains [Employee], FindAndReplace.sql will create an alter script to replace [Employee] with [vEmployee]. The alter and backup scripts will be stored in the Temp folder on the C drive.

After FindAndReplace.sql runs, it outputs a message like that in Figure 1. In this case, the message lets you know that five stored procedures contain the keyword [Employee]. So, C:Temp will contain five alter scripts and five backup scripts.

Here’s how I use FindAndReplace.sql in my job: I create a dummy database on my development server, copy the stored procedures on my production server to the development server, and run FindAndReplace.sql on the development server. After it runs, I use the open-source WinMerge tool (winmerge.org) to compare the set of alter scripts against the set of backup scripts so that I can review the changes the alter scripts will make. When I’m satisfied that no definitions will be altered incorrectly, I run the alter scripts on my production server.

You can download FindAndReplace.sql by clicking the 99461.zip hotlink above. This stored procedure works with SQL Server 2005 only and requires that xp_cmdshell be enabled. The SQL Server 2005 engine must have permission to write files to the specified output folder.

—Louis Nguyen, DBA, Centex Homes