Find a Stored Procedure by Text.

This is a response to @damagedwoods. It's a script that searches through all stored procedures for a given text string. It's clunky, of course, and basically just parses each sp name through sp_helptext and searches the results for text.  It's not very fast, but it does it's job. Hope you find it useful.  If not, I'd recommend SQL Search by Red Gate.  It's free, and works even better.

Script Ahoy!

   1:   

   2:   

   3:  /* Find stored procedure by text string */

   4:  DECLARE 

   5:    @SearchString varchar(4000), -- The text to search for

   6:    @ProcName varchar(500), -- The name of the proc we're checking.

   7:    @ErrorMessage varchar(4000) -- Error placeholder.

   8:   

   9:  SET @SearchString = (SELECT '')

  10:   

  11:  IF (SELECT OBJECT_ID('tempdb.dbo.#Search')) IS NOT NULL

  12:  DROP TABLE #Search

  13:   

  14:  IF (SELECT OBJECT_ID('tempdb.dbo.#Results')) IS NOT NULL

  15:  DROP TABLE #Results

  16:   

  17:  IF (SELECT OBJECT_ID('tempdb.dbo.#ProcList')) IS NOT NULL

  18:  DROP TABLE #ProcList

  19:   

  20:  CREATE TABLE #ProcList ( -- The list of procedures to search.

  21:    [Name] varchar(500)

  22:  )

  23:   

  24:  CREATE TABLE #Search ( -- A place to search them.

  25:    ProcName varchar(500),

  26:    TextLine varchar(4000)

  27:  )

  28:   

  29:  CREATE TABLE #Results ( -- The place to put the results.

  30:    ProcName varchar(500),

  31:    TextLine varchar(4000)

  32:  )

  33:   

  34:  SET @ErrorMessage = (SELECT NULL) -- No errors yet...

  35:   

  36:  IF (SELECT @SearchString) IS NULL -- You forgot to put in a search string.

  37:  BEGIN

  38:    SET @ErrorMessage = (SELECT 'You did not provide anything to search for')

  39:    GOTO EXITPROC -- Fatal Error.

  40:  END

  41:   

  42:  -- Get the list of procedures to search from dbo.sysobjects.

  43:  INSERT INTO #ProcList

  44:    SELECT [Name] 

  45:    FROM dbo.sysobjects

  46:    WHERE xtype = 'P'

  47:   

  48:  WHILE (SELECT COUNT(*) FROM #Proclist) > 0 -- Still have procs to check...

  49:  BEGIN

  50:   

  51:    SET @ProcName = (SELECT TOP 1 [Name] FROM #Proclist)

  52:     

  53:    BEGIN TRY

  54:   

  55:      INSERT INTO #Search(TextLine)

  56:      EXEC dbo.sp_helptext @ProcName 

  57:     

  58:      UPDATE #Search

  59:      SET ProcName = @ProcName 

  60:      WHERE ProcName IS NULL

  61:   

  62:    END TRY

  63:   

  64:    BEGIN CATCH

  65:    

  66:      SET @ErrorMessage = (SELECT ERROR_MESSAGE() + 

  67:      '  --  Unable to get text for procedure ' + @ProcName) -- It could happen...

  68:    

  69:    END CATCH -- Non-fatal. Keep going.

  70:     

  71:    DELETE FROM #ProcList 

  72:    WHERE [Name] = @ProcName -- Done with that one.

  73:   

  74:  END

  75:   

  76:  -- Gather the results into another table, suitable for display.

  77:  INSERT INTO #Results (ProcName, TextLine)

  78:  SELECT ProcName, TextLine 

  79:  FROM #Search

  80:  WHERE TextLine LIKE '%' + @SearchString + '%'

  81:   

  82:  IF (SELECT COUNT(*) FROM #Results) = 0 -- No results.

  83:  BEGIN

  84:    SET @ErrorMessage = 'Specified search string was not found.'

  85:    GOTO EXITPROC -- Fatal error.

  86:  END

  87:   

  88:  SELECT * FROM #Results -- Display what we have.

  89:   

  90:  EXITPROC:

  91:  IF (SELECT @ErrorMessage) IS NOT NULL

  92:  BEGIN

  93:    RAISERROR(@ErrorMessage,16,1)

  94:  END