Search for text in procedures (no cursors) UPDATE

,

This is an update to the Search for text in procedures (no cursors) function (excellent script), which lists all of the objects (searching in syscomments, so it excludes tables). I have added a 2nd column to the output of the object type.

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

Rate

Share

Share

Rate