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

,

This script creates a stored procedure which helps to find text in all of your code objects in all your databases. It searches the code of SP's, UDF's, views, triggers, and it searches in all databases.
Output is a result set with DbName, ObjectName and ObjectType.
Advantages are
- works reliable also with source codes longer than 4000 byte.
- apparently runs faster than scripts using sp_helptext.

Usage: xFind 'SearchForMe'

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

Rate

Share

Share

Rate