Stored Procedures Code Search

Suppose you have 1000 stored procedures in the database. Now you have to find something out there in the code. Suppose you want to drop the redundant database table and don't know about whether some procedure uses it. This is a small procedure I build, that would help you to perform such search:

create proc P_TEXT_SEARCH_SPS
(
   @p_word varchar(30)
)
as
begin
   declare proccurs cursor
   for
      select C.[text], O.[name]
      from 
         sysobjects O join syscomments C on
         C.[id] = O.[id]  
      where O.xtype = 'p' and O.status > 0
   open proccurs
   declare @ptext varchar(2000)
   declare @pname char(40)
   fetch next from proccurs into @ptext, @pname
   while @@fetch_status = 0
   begin
      print @pname + ' = ' + 
         convert(varchar(5), charindex(@p_word, @ptext, 0))
      fetch next from proccurs into @ptext, @pname
   end
   close proccurs
   deallocate proccurs
end 

That's it. Happy searching :)

Related Posts:

• SQLBPA and order by constant
• T-SQL Paging Series. RowCount
• Check Constraint vs. Rule
• Rendering depends on HTML source formatting
• sp_sproc_columns and Profiler
• SQL Function for Date Sequences (Version 2.1)
• MSSQL: Some Undocumented Features
• Generate Calendar Dates. Version 2.0
• My howto on sqljunkies
• How to PIVOT Tables in MSSQL 2000

Thanks, this was helpful!

So I improved on it!

It's a table-valued UDF and searches lots of other stuff. Also returns almost the whole source code (show results in Grid):

CREATE function dbo.fn_util_SearchSprocs (@p_word varchar(30))
returns @SearchResults table
(
Type char(2),
[Name] sysname,
[Offset] varchar(5),
[Text] nvarchar(3000)
)
as
begin
insert into @SearchResults
select so.xtype, so.[name], convert(varchar(5), charindex(@p_word, sc.[text], 0)), cast(sc.[text] as nvarchar(3000))
from sysobjects as so
inner join syscomments as sc
on sc.[id] = so.[id]
--sprocs, triggers, UDFs, table-valued UDFs, views, defaults, and check-constraints
where so.xtype IN ('P', 'TR', 'FN', 'TF', 'V', 'D', 'C')
and so.status > 0
and charindex(@p_word, sc.[text], 0) > 0

return
end

Forgot to mention this for newbies:

To try this out, run this:

select *
from dbo.fn_util_SearchSprocs('dave')

That will find all the code with your name in it. If your name is 'dave', that is :)

Dave