Let's say you work with a huge MS SQL Server database containing hundreds of user-created stored procedures. You need to find a stored procedure that performs a certain function necessary for your application, but you don't want to spend hours running sp_helptext and examining the code for 300 stored procedures. What can you do?
Here's a stored procedure I created called sp_FindProcTxt that allows you to search all the user stored procedures in your database, returning the ones whose code contains the search string you specify. There are 2 modes: 0 (the default) returns only the names of the SPs; Mode 1 returns the entire code of the SPs.
For example, if you need to find a SP that re-indexes all the tables in your database, and thus you want to list all SPs (along with their code) that contain the word "index," you would type:
EXEC sp_FindProcTxt 'index', 1
Below is the code (note: the sysname datatype works only with SQL 2000; for SQL 7 you have to use nvarchar(128)):
create procedure sp_findproctxt
/*
lists all stored prodedures that contain the
text in @sstring in their code
*/
@sstring sysname,
@mode int = 0
/*
0 = return sp name only
1 = return entire code of sp
*/
as
if @mode = 0 --we just want the names
begin
select o.name
from sysobjects o
join syscomments c
on o.id = c.id
where o.type = 'p'
and charindex(@sstring, c.text) >= 1
end
if @mode = 1 --we need the entire code
begin
declare findproctxt cursor
for
select o.name
from sysobjects o
join syscomments c
on o.id = c.id
where o.type = 'p'
and charindex(@sstring, c.text) >= 1
declare @proc sysname
open findproctxt
fetch next from findproctxt
into @proc
while (@@fetch_status = 0)
begin exec sp_helptext @proc
fetch next from findproctxt
into @proc
end
close findproctxt
deallocate findproctxt
end
For More Information
