Searching Stored Procedures Across SQL Server Databases

Here’s a little utility SP I wrote which searches all stored procedures in all databases in your SQL Server Instance for an input string.
In SQL Server Management Studio 2008 there is an option to do filtered searches but it only look inside the selected database. Now honestly I am not sure you should be in a position where you would want to search stored procedures across Databases, but if you do find the need, hope this is useful!
Example:
If you compile this utility into DB1, then simply say :

EXEC DB1..sp_SearchInSPs 'mySearchString'

[UPDATE:]Download Link:

sp_SearchInSPs.sql

Code:

  1: SET ANSI_NULLS ON
  2: GO
  3: SET QUOTED_IDENTIFIER ON
  4: GO
  5: -- =============================================
  6: -- Author:  Sajjan Sarkar with help of SungUngKim
  7: -- Create date: April 26, 2011
  8: -- Description: stored procedure to search all SPs for a given string in ALL databases. 
  9: -- =============================================
 10: -- Sample to run
 11: --EXEC DB1..sp_SearchInSPs 'BillingOvertimeCalcFactor'
 12: 
 13: ALTER PROCEDURE [dbo].[sp_SearchInSPs] 
 14: (
 15:  -- Add the parameters for the stored procedure here
 16:     @SearchString VARCHAR(100) = ''   
 17: )    
 18: AS 
 19:     BEGIN
 20:  --Declare 
 21:         DECLARE @SString NVARCHAR(50)
 22:         DECLARE @getdbname SYSNAME
 23:         DECLARE @sqlstm NVARCHAR(1000)
 24:         CREATE TABLE #tmp
 25:             (
 26:               DBName VARCHAR(100) ,
 27:               SPName VARCHAR(100)
 28:             )
 29:         DECLARE dbname CURSOR
 30:         FOR
 31:             --get all the names of the Databases in order by name
 32:   SELECT  '[' + name + ']'
 33:             FROM    master.dbo.sysdatabases            
 34:             ORDER BY name 
 35:         OPEN dbname
 36: 
 37:   --Get the first Name
 38:         FETCH NEXT FROM dbname INTO @getdbname
 39: 
 40:         WHILE @@FETCH_STATUS = 0 
 41:             BEGIN
 42:                 PRINT @getdbname
 43:     --set the search string
 44:                 SET @SString = @SearchString
 45: 
 46:     --append the search pattern
 47:                 SET @SString = '%' + @SString + '%'
 48:                 SET @sqlstm = 'SELECT  DISTINCT ''' + @getdbname + ''',
 49:         s.name     
 50:         FROM    ' + @getdbname + '.dbo.syscomments c
 51:         INNER JOIN  ' + @getdbname + '.dbo.sysobjects s
 52:         ON      c.id = s.id
 53:         WHERE   s.type IN ( ''p'', ''tr'' )
 54:         AND c.text LIKE ''%'+@SString+'%''
 55:         ORDER BY [Name]'
 56: 
 57: 
 58:   --Execute the Query
 59:                 INSERT  INTO #tmp
 60:                         EXEC ( @sqlstm
 61:                             )
 62:                 FETCH NEXT FROM dbname INTO @getdbname
 63:             END
 64: 
 65:   --Close the Cursor and Deallocate it from memory
 66:         CLOSE dbname
 67:         DEALLOCATE dbname 
 68:   
 69:         SELECT  *
 70:         FROM    #tmp AS T
 71:         DROP TABLE #tmp
 72: 
 73:     END
 74: