-- Altered slightly from http://www.knowdotnet.com/articles/storedprocfinds.html
CREATE PROCEDURE Find_Text_In_SP_or_FN (@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' OR SO.Type LIKE '%F%')
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO