Search a text in stored procedure source code

use master

go

-- =============================================

-- Description: Search a text in stored procedure source code.

-- @text - any text to find, search is done by like '%text%'

-- @dbname - database where to search,

--         - if omitted, all databases in the SQL server instance

-- =============================================

alter PROCEDURE [dbo].[sp_FindTextInsideProcedure]

  @text varchar(250),

  @dbname varchar(64) = null ,

  @OrderByColumn int = 1 ,

  @Level int = null

AS BEGIN

SET NOCOUNT ON;

if ( @dbname is null or @dbname like '%[_]%' or @dbname like '%[%]%' ) and @Level is null

  begin

  /*

   -- cursor programming

  

    --enumerate all databases.

  DECLARE #db CURSOR FOR Select Name from master..sysdatabases where ( @dbname is null or Name like @dbname   )

  declare @c_dbname varchar(64)

     

  OPEN #db FETCH #db INTO @c_dbname

  while @@FETCH_STATUS <> -1 --and @MyCount < 500

   begin

     execute sp_FindTextInsideProcedure @text=@text, @dbname = @c_dbname

     FETCH #db INTO @c_dbname

   end 

  CLOSE #db DEALLOCATE #db

 

  */

      -- no cursor

      --enumerate all databases.

      Select Name

      into #db

      from master..sysdatabases

      where ( @dbname is null or Name like @dbname   )

      declare @c_dbname varchar(64)

      set @c_dbname = (select top 1 Name from #db)

      declare @result table ( db varchar(64) , name varchar(256) , [definition] nvarchar(max))

      while ( @c_dbname is not null )

      begin

            insert into @result

            execute sp_FindTextInsideProcedure @text=@text, @dbname = @c_dbname , @Level =1

            delete #db

            where name = @c_dbname

            -- get the next database name

            set @c_dbname = (select top 1 Name from #db)

      end 

      drop table #db

      select * from @result r

      order by case @OrderByColumn

                              when 1 then r.db

                              when 2 then r.name 

                              when 3 then r.[definition] 

                              else  r.db

                  end

     

end --if @dbname is null

else

begin --@dbname is not null

  declare @sql varchar(4000)

  --create the find like command

  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '

                        + ' from ['+@dbname+'].sys.sql_modules m '

                        + ' inner join ['+@dbname+']..sysobjects o '

                        + '         on m.object_id=o.id'

                        + ' where [definition] like ''%'+@text+'%'''

  if (@Level is null)

  begin

      select @sql = @sql + 'order by case ' + convert(varchar , @OrderByColumn ) 

                        + '                     when 1 then ''' + @dbname + ''''

                        + '                     when 2 then  o.name  '

                        + '                     when 3 then m.definition  '

                        + '                     else   ''' + @dbname + ''''

                        + '         end'

  end

  execute (@sql)

end --@dbname is not null

END