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:
- @myfind, which
specifies the keyword to
search for
- @myreplace, which is the replacement keyword
- @filepath, which specifies the folder in which to
store the alter and backup scripts
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