SP to find text in code objects in all user db's.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create procedure xFind
  @Search varchar(255)
as
begin
  set nocount on

  declare
    @Sql1 nvarchar(4000),
    @Sql2 nvarchar(4000),
    @DbName sysname,
    @curDatabases cursor


  create table #Results(
    Db sysname,
    ObjName sysname,
    ObjType char(2))


  -- for all user databases
  set @curDatabases = cursor fast_forward for
    select name
    from   master..sysdatabases
    where  name not in ('master', 'tempdb', 'model', 'msdb', 'pubs', 'tempdb', 'northwind')

  open @curDatabases
  fetch next from @curDatabases into
    @DbName

  while @@fetch_Status = 0
  begin

    print 'Searching in ' + @DbName + '...'

    set @Sql1 = '
    set nocount on

    declare
      @ObjId int,
      @ObjName sysname,
      @ObjTextpart nvarchar(4000),
      @ObjTextpart_id int,
      @ObjText nvarchar(4000),
      @ObjType char(2),
      @i_s int,
      @curObjects cursor,
      @curObjectTextparts cursor


    -- for all procedures/functions
    set @curObjects = cursor fast_forward for
      select name,
             id,
             type
      from   [' + @DbName + ']..sysobjects
      where  type in (''FN'', ''IF'', ''P'', ''TF'', ''TR'', ''V'')
      order by name

    open @curObjects
    fetch next from @curObjects into
      @ObjName,
      @ObjId,
      @ObjType

    while @@fetch_status = 0
    begin

      -- for all object textparts
      set @curObjectTextparts = cursor fast_forward for
        select text,
               colid
        from   [' + @DbName + ']..syscomments
        where  id = @ObjId
        order by colid

      open @curObjectTextparts
      fetch next from @curObjectTextparts into
        @ObjTextpart,
        @ObjTextpart_id

      while @@fetch_status = 0
      begin

        while 1=1  -- object textpart shifts
        begin

          if @ObjTextpart = ''''
            break  -- get 2nd textpart

          if @ObjTextpart_id = 1
          begin
            set @ObjText = @ObjTextpart
            set @ObjTextpart = ''''
          end
          else
          begin
            set @ObjText = substring(@ObjText, 2001, 2000) + left(@ObjTextpart, 2000)
            set @ObjTextpart = substring(@ObjTextpart, 2001, 2000)
          end
'
set @Sql2 = '

          set @i_s = charindex(''' + @Search + ''', @ObjText)
          if @i_s <> 0  -- search string found?
          begin

            insert into #Results(Db, ObjName, ObjType)
            values(''' + @DbName + ''',
                   @ObjName,
                   @ObjType)
          end  -- search string found


        end  -- object textpart shifts loop

        fetch next from @curObjectTextparts into
          @ObjTextpart,
          @ObjTextpart_id

      end  -- object textparts loop

      close @curObjectTextparts
      deallocate @curObjectTextparts


      fetch next from @curObjects into
        @ObjName,
        @ObjId,
        @ObjType

    end  -- objects loop

    close @curObjects
    deallocate @curObjects
    '
    exec (@Sql1 + @Sql2)

    fetch next from @curDatabases into
      @DbName

  end

  close @curDatabases
  deallocate @curDatabases

  select distinct *
  from   #Results
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO