Find a specific text in stored procedures

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print

Sometimes, when we've multiple databases, with multiple stored procedures (hundreds of them), finding a procedure that performs a specific task, or that selects a specific field of a table, can be a really hard task (even when the procedures have an expressive name)


This procedure accepts a string, and search for all the procedures that contains the specified string in its body.



CREATE PROCEDURE Find_Text@stringToSearch varchar(100)AS SET @string_To_Search ='%'+@string_To_Search+'%' SELECT DISTINCT  SO.Name FROM sysobjects SO (NOLOCK)         INNER JOIN syscomments SC (NOLOCK) ON SO.Id = SC.Id AND SO.Type='P' AND SC.Text LIKE @string_To_Search ORDER BY SO.Name


GO

 : hgeraldino     Reply  

Replies (1)

profile
KINGSLEY TAGBO

This is a useful sproc and a good post [H]



Post A Reply

 Questions & Answers