Find all Occurrences of Text in all Stored Procedures in all Databases

Sometimes you'd like to find all occurrences of a piece of text in every stored procedure, trigger, etc., possibly to replace it with a new value. The script below will do just that; it returns the database name, the object name, and the object type. Note that it works for both SQL Server 2000 and post-SQL Server 2000 versions.

One thing to note is that the @TextToFind uses the LIKE operator with its ESCAPE argument set to the dollar sign symbol. I added the ESCAPE argument just to make it easier to search for text such containing the underscore character, such as 'My_HardToFind_Thing". Of course, if you're searching for text with a dollar sign symbol in it, you'll want to change the ESCAPE argument to something else.

(The script below can also be run in CMS, which will allow you to also return results across server groups.)

-- Simply set the text you want to look for here. 
declare @TextToFind nvarchar(4000)
set @TextToFind = 'THIS IS MY TEXT TO FIND'

declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

     declare @DatabaseName sysname
     declare @ProcName     sysname
     declare @ProcType     nvarchar(60)

     if left(convert(nvarchar, serverproperty(''productversion'')), 1) <> ''8''
     begin
         declare cur cursor local fast_forward for
            select distinct ''?''          as ''Database Name''
                          , o.name         as ''Object Name''
                          , o.type_desc    as ''Object Type''
              from sys.sql_modules   m
              join sys.objects       o
                on m.object_id = o.object_id
             where m.definition like ''%' + @TextToFind + '%'' escape ''$''

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    else
    begin
         declare cur cursor local fast_forward for
            select distinct ''?''               as ''Database Name''
                          , object_name(c.id)   as ''Object Name''
                          , o.xtype             as ''Object Type''
              from syscomments   c
              join sysobjects    o
                on c.id = o.id
             where c.[text] like ''%' + @TextToFind + '%'' escape ''$''
               and objectproperty(c.id, ''isprocedure'') = 1

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    
    '

exec sp_msforeachdb @Sql