Friday, November 18, 2005

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

5 comments:

Santhi said...

Really an useful post.

Parameshwaran.K(Param) said...

whats the difference between the SP's created in Master DB & Other DB?Are you searching the name of the SP or the content in the SP?can you clarify this?

Vadivel said...

>>whats the difference between the SP's created in Master DB & Other DB?

I guess your question is about why we shouldn't use "SP_" for stored procs which are created in DB other than MASTER. Its like this:

As you might be knowing the system stored procs would be prefixed with "SP_". That said, if we prefix "sp_" in our user-defined stored procedure it would bring down the performance ..bcoz SQL Server always looks for a stored procedure beginning with "sp_" in the following order:

1) Master DB,
2) The stored procedure based on the fully qualified name provided,
3) The stored procedure using dbo as the owner, if one is not specified.

So, when you have the SP with the prefix "sp_" in the DB other than master, the Master DB is always checked first, and if the user-created SP has the same name as a system stored proc, the user-created stored procedure will never be executed. Hope this helps!!

Vadivel said...

>>Are you searching the name of the SP or the content in the SP

I am searching within the content of the all SP's. I would suggest you to try the code snippet to have a better understanding of it.

Anonymous said...

Thank you very much for the stored procedure. I have included it in my utilities chest!

I used following code to search for all tables that contain a column:

Select o.name, suser_sname(o.uid), c.name
From sysobjects o inner join syscolumns c
on o.id = c.id
where o.type = 'U' -- U for user table - see sysobjects table in bol for more object types
and c.name like '%search_column_name%';
go

Piyush Varma