Search for text in procedures (no cursors)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  FUNCTION dbo.fn_procedure_search (@search_value varchar(255), @does_not_contain varchar(255))
RETURNS @out_table TABLE (proc_name varchar(255))
AS
BEGIN

/*
select * from dbo.fn_procedure_search ('value1', null)
select * from dbo.fn_procedure_search ('value2', 'value3')
*/
INSERT @out_table
select distinct object_name(id) from syscomments where text like '%' + @search_value + '%'

if @does_not_contain is not null and rtrim(@does_not_contain) <> ''
begin
delete o 
from syscomments s, @out_table o 
where object_name(s.id) = o.proc_name
and s.text like '%' + @does_not_contain + '%' 
end

RETURN 
END


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO