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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s