Quick search within ALL stored procedures ...

This article would explain in detail the methods involved in searching strings within ALL stored procedures.

I am sure there might have been situation where you want to find out a stored procedure where you remember writing some complex logic. Won't it be nice if we can find out that stored procedure where we have already written that important piece of code .. so that we can reuse? If your answer is "yes" read on.

Points to note before executing this SP:

1. I have written 2 methods for this purpose. If we want this SP to be in the MASTER database then set @method =1. If not set it to 2

2. If @method is set to 2 then it is advisable to change the SP name. As you know only SP's which exist in MASTER database needs to be prefixed with "SP_" (for performance reason).

The Stored Procedure:

Create Procedure sp_searchForStoredProc
(
@searchString varchar(100)
)
As
/**************************************************
Stored Procedure: sp_searchForStoredProc CreatiOn Date: 11/18/2005
Written by: Vadivel Mohanakrishnan

Purpose: List out all SPs where the particular search string exists.
1)If you want to create this SP in MASTER database then choose Method 1.
2)If you want to create in Individual database then Go for Method 2

Since most of us would prefer having it in MASTER DB i have prefixed the SP with "sp_". If you
plan to install in some user created DB .. I strongly suggest to remove the prefix.

Output Parameters: none
Return Status: 0-Sucess, 1-Failure

Called By:
Component:
Calls:

Data ModificatiOns:

Database:
System Tables: SysComments, SysObjects
Views : InformatiOn_Schema.Routines
FunctiOns :

Updates:
Date Author Purpose
11/18/2005 Vadivel Mohanakrishnan Create
**************************************************/
Set nocount on

Declare @method int
Set @method = 1

If (@method = 2) and (upper(db_name()) = upper('Master'))
Begin
RaisError 60001 'Error in Installation!! Since you are running from MASTER database, Set @method=1.'
Return 0
End

--Append the wildcard % before and after the search string
Set @searchString = '%' + @searchString + '%'

If @method = 1
Begin

/* Method 1 :: For Master Database */

Select
distinct(SO.[name]) as 'Stored Procedure Name'
From
SysComments SC,
SysObjects SO
Where
SO.ID = SC.ID and
cateGory <> 2 and
PatIndex( @searchString, text) > 0
Order By
[name]
End
Else
Begin

/* Method 2 :: For Individual Database
Double check whether you have removed the prefix "sp_" from the SP name.
*/

Select
Routine_Name as 'Stored Procedure Name'
From
InformatiOn_Schema.Routines
Where
Routine_type = 'procedure' and
ObjectProperty(Object_Id(Routine_Name),'IsMsShipped') = 0 and
PatIndex( @searchString, Routine_DefinitiOn) > 0
End

If @@Error <> 0
Return (1)
Return 0
Go

Syntax for checking this Stored Procedure:

Exec usp_searchForStoredProc 'SearchString Comes here'

Examples:
1. If the SP has been created in MASTER database then
Exec sp_searchForStoredProc 'delete'

2. If the SP has been created in some other DB then,
Exec usp_searchForStoredProc 'delete'

Clean Up:
Drop proc sp_searchForStoredProc