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.