Secret Squirrel

This post is just for holding nice little SQL scripts that seem like they could be useful things to have in the toolbox.

First up we have a routine that can be used to search all the stored procedures in a database for the supplied string:

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

And its close personal friend for searching the names of stored procs for the specified string:

CREATE PROCEDURE Find_SPName_With_Text
   @StringToSearch varchar(100)
AS
   SET @StringToSearch = '%' + @StringToSearch + '%'
   SELECT DISTINCT SO.NAME
   FROM SYSOBJECTS SO (NOLOCK)
   WHERE SO.TYPE = 'P'
   AND SO.NAME LIKE @StringToSearch
   ORDER BY SO.Name
GO

The above sprocs were shamelessly stolen from knowdotnet.com