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  :	http://www.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