Can I search for a string in a set of stored procedures?

[COHENMARVIN]

I found that I'm making an error in my 'case' statements, and now I
want to go through a database full of many stored procedures to find
that error in some of them and to fix it. Is there any way to do a
search through multiple stored procedures at once? I don't see one in
my version of Sql Server Management Studio?
Thanks,
Marv

[Ed Murphy]

CO*********@lycos.com wrote:

I found that I'm making an error in my 'case' statements, and now I
want to go through a database full of many stored procedures to find
that error in some of them and to fix it. Is there any way to do a
search through multiple stored procedures at once? I don't see one in
my version of Sql Server Management Studio?

I think this is right (I mostly still work with 2K which uses older
methods):

select o.name
from sys.objects o
join sys.sql_modules m on o.object_id = m.object_id
where m.definition like '%text%'

[nidaar]

On Jun 26, 11:08*am, [e-mail] wrote:

I found that I'm making an error in my 'case' statements, and now I
want to go through a database full of many stored procedures to find
that error in some of them and to fix it. *Is there any way to do a
search through multiple stored procedures at once? *I don't see one in
my version of Sql Server Management Studio?
Thanks,
Marv

Alternatively stored procedures of the database can be scripted by
right clicking on the database name and selecting Generate Scripts...
under Tasks in SSMS. This creates a .sql file with all the stored
procedures (formatted). Then Find/Replace command can be used to
search / update. Once all changes are made, executing the script can
update all stored procedures (if "Add If Not Exists..." option is not
selected during the Generate Scripts Wizard).

Also "Database Publishing Wizard" can be used (free downloadable from
Microsoft) to script each stored procedure to individual files. Then a
program like SQL Server Business Intelligent Development Studio can be
used to Find/Replace in files. This method can be useful if one also
wants to add the scripts to source control.

James