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.