I have a solution that I use to find occurrences of a string within any stored procedure in a database. Although is uses a cursor, I have found the performance to be more than acceptable. This solution also has the merit of telling you the line number that the match was found on.

CREATE proc dbo.zspFind

@Search varchar(100)

as

DECLARE cur CURSOR

READ_ONLY

FOR select name, syscomments.id, colid

from syscomments

join sysobjects on syscomments.id=sysobjects.id

where xtype='P' and category=0

order by name, colid

DECLARE @name varchar(100)

DECLARE @id int

DECLARE @colid int

DECLARE @buf varchar(8000)

DECLARE @pos1 int

DECLARE @pos2 int

DECLARE @line varchar(4000)

DECLARE @lineno int

declare @out table (Name varchar(100), line int, [text] varchar(1000))

OPEN cur

set @buf=''

set @lineno=1

FETCH NEXT FROM cur INTO @name, @id, @colid

WHILE (@@fetch_status -1)

BEGIN

select @buf=@buf+cast([text] as varchar(4000)) from syscomments where id=@id and colid=@colid

set @pos1=1

select @pos2=charindex(char(10), @buf, @pos1)

while @pos2>0

begin

select @line=substring(@buf, @pos1, @pos2-@pos1), @lineno=@lineno+1

if charindex(@Search, @line)>0

begin

set @line =replace(@line, char(9), '')

insert into @out values (@name, @lineno, @line)

end

select @pos1=@pos2+1

select @pos2=charindex(char(10), @buf, @pos1)

end

set @buf=substring(@buf, @pos1, 4000)

FETCH NEXT FROM cur INTO @name, @id, @colid

if @colid=1 set @lineNo=1

END

CLOSE cur

DEALLOCATE cur

select * from @out