Kaze,

You can use a script or stored procedure to search through the system table
SYSCOMMENTS which contains the text of all stored procedures (and other
objects) within a given database. I have a stored procedure that searches
this table for all objects. If you choose to place this in the master db,
you can run it from any database and it will find text for your sp.

Usage: sp_find 'Customer_Table'

would find all instances of the phrase "Customer Table" within all objects,
including Stored Procs, UDFs, etc. The output simply procudures a list of
objects prefaced by "sp_helptext" to make it easier to immediately read the
text of the proc.

Hope this helps,

Scott



create procedure SP_FIND
(@findtext varchar(100) = null)
as

select DISTINCT Text = 'sp_helptext ' + convert(sysname,object_name(id))
from syscomments
where text like ('%' + @findtext + '%')



-----Original Message-----
From: Kaze [mailto:[Email Address Removed] Friday, April 25, 2003 1:49 PM
To: LazyDBA.com Discussion
Subject: Searching for Key words in Store Procedures



Hi Folks,

Is there anyway to get get a result set of all the user store procedures in
a database containing a key word. We have hundreds of store procedures in
one of my production database and currently i'm scripting all of these store
procedures and using ultra edit to find these words. The problem is it's
such a pain having to scroll through the scripts to find a key word and then
have to scroll back up to see which store procedure it is from. There's got
to be an easier way to handle this. Please Advise....thank you!

Kaze



---------------------------------
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
MS Sql Server LazyDBA home page