Ever needed to search for a string in your stored procedures?

I was digging through an older database I am rewriting and I stumbled on this little stored procedure.  It uses the internals of SQL Server to search the stored procedures for a provided string.  Just one of those bits of code we are all meaning to write but never do.

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   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 @stringtosearch
   ORDER BY SO.Name
GO

I'm not even sure who wrote it but it seems like something handy to have around.

Comments are closed

Bill Sempf

Husband. Father. Pentester. Secure software composer. Brewer. Lockpicker. Ninja. Insurrectionist. Lumberjack. All words that have been used to describe me recently. I help people write more secure software.

profile for Bill Sempf on Stack Exchange, a network of free, community-driven Q&A sites

MonthList