if object_id('sp_search') is not null
        drop procedure sp_search
go
create procedure sp_search
(
@spname varchar(30),
@searchstr varchar(255)
)
as
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    14 Oct 1999                                             */
/*      Description:    This SP can be used to search for number of occurences  */
/*                      of a string in a SP.                                    */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
declare @id int, @text varchar(255), @textptr varbinary(16), @occur int,
        @offset int, @length int
if charindex('.', @spname) > 0
begin
        raiserror('Invalid sp name. You can only search sps without user prefix',
                        1,2) with seterror
        return(-1)
end
create table #sps_text (text text null)
insert #sps_text values('')
select @textptr = textptr(text), @length = datalength(@searchstr) from #sps_text
create table #sps_names (id int, occurences int)
declare sps insensitive cursor for
 select id from sysobjects
  where lower(name) like '%' + lower(@spname) + '%' and type = 'P'
open sps
while('FETCH IS OK' = 'FETCH IS OK')
begin
        fetch next from sps into @id
        if @@fetch_status < 0 break
        -- initialize the text column and get a pointer
        updatetext #sps_text.text @textptr 0 NULL NULL
        declare sp_text insensitive cursor for
         select text from syscomments
          where id = @id
        open sp_text
        fetch next from sp_text into @text
        while(@@fetch_status >= 0)
        begin
                updatetext #sps_text.text @textptr NULL 0 @text
                fetch next from sp_text into @text
        end
        close sp_text
        deallocate sp_text
        select @offset = patindex('%' + @searchstr + '%', text) - 1, @occur = 0
        from #sps_text
        while(@offset >= 0)
        begin
                updatetext #sps_text.text @textptr @offset @length NULL
                select @occur = @occur + case when (@offset >= 0) then 1 else 0 end
                select @offset = patindex('%' + @searchstr + '%', text) - 1
                from #sps_text
        end
        if @occur > 0
                insert #sps_names values(@id, @occur)
end
close sps
deallocate sps
select object_name(id) as name, occurences from #sps_names
return(0)
go
grant execute on sp_search to public
go

This page was last updated on May 01, 2006 04:28 PM.