MS SQL Server Stored Procedure Search

Hi All,

Recently I had to write the following script on MS SQL Server to create a proc that would search through all the procedures in the database for a given search phrase. Guess it would be helpful for someone else as well.

create proc adm_SearchProcs
@searchText varchar(max)
as
begin
select ‘exec sp_helptext ‘ +””+ specific_schema +’.’ + routine_name +””
from information_schema.routines
where routine_definition like ‘%’+@searchText+’%’
order by 1
end

Happy Coding!!

Rasika