Search for text in procedures (no cursors) UPDATE

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION dbo.fn_obj_search (
@search_value varchar(255), 
@does_not_contain varchar(255) = null
)
RETURNS @out_table TABLE (obj_name varchar(255), obj_type varchar(50))
AS
BEGIN

/*
select * from dbo.fn_obj_search ('value1', null)
select * from dbo.fn_obj_search ('value2', 'value3')
*/
INSERT @out_table
select distinct object_name(syscomments.id),
CASE xtype 
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END
from syscomments inner join sysobjects on
syscomments.id = sysobjects.id
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.obj_name
and s.text like '%' + @does_not_contain + '%' 
end

RETURN 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO