Searching for Text Within a Stored Procedure

Here is a cool sp that will find any text within a stored procedure. Add this your Master database.

CREATE PROCEDURE [dbo].[Find_text_in_sp] @text VARCHAR(250),@dbname VARCHAR(64)

= NULL

AS

  BEGIN

      SET nocount ON;

      IF @dbname IS NULL

        BEGIN

            --enumerate all databases.  

            DECLARE #db CURSOR FOR

              SELECT name

              FROM   MASTER..sysdatabases

            DECLARE @c_dbname VARCHAR(64)

            OPEN #db

            FETCH #db INTO @c_dbname

            WHILE @@FETCH_STATUS <> –1 --and @MyCount < 500  

              BEGIN

                  EXECUTE Find_text_in_sp

                    @text,

                    @c_dbname

                  FETCH #db INTO @c_dbname

              END

            CLOSE #db

            DEALLOCATE #db

        END --if @dbname is null  

      ELSE

        BEGIN --@dbname is not null  

            DECLARE @sql VARCHAR(250)

            --create the find like command  

            SELECT @sql = ‘select ”’ + @dbname +

                          ”’ as db, o.name,m.definition ‘

            SELECT @sql = @sql + ‘ from ‘ + @dbname + ‘.sys.sql_modules m ‘

            SELECT @sql = @sql + ‘ inner join ‘ + @dbname +

                          ‘..sysobjects o on m.object_id=o.id’

            SELECT @sql = @sql + ‘ where [definition] like ”%’ + @text + ‘%”’

            EXECUTE (@sql)

        END --@dbname is not null  

  END 

Tip By: David McCarter -orginal code from: http://www.motobit.com/tips/detpg_sql-find-text-stored-procedure/