Search View/Function/Procedure-Definition Text in SQL-Server

SearchInViewsFunctionsProcedures.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SCHEMA_SearchInViewsFunctionsProcedures]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SCHEMA_SearchInViewsFunctionsProcedures]
GO




-- ======================================================
-- Author:		  Stefan Steiger
-- Create date:   18.03.2016 
-- Last modified: 18.03.2016 
-- Description:	  
-- ======================================================
-- EXECUTE sp_SCHEMA_SearchInViewsFunctionsProcedures 'V_AP_BERICHT_DATA_REM_Raumliste' 
CREATE PROCEDURE [dbo].[sp_SCHEMA_SearchInViewsFunctionsProcedures]
	@in_searchText AS nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @escapedSearchText nvarchar(1000), @dif integer, @i integer
	SET @escapedSearchText = N''
	SET @dif = LEN(@in_searchText) + 1 
	SET @i = 1
	WHILE @i < @dif
	BEGIN	
		SET @escapedSearchText = @escapedSearchText + '\' + SUBSTRING(@in_searchText,@i, 1) 
		SET @i = @i + 1
	END 
	-- PRINT '|' + @escapedSearchText + '|'
	
	
	SELECT ROUTINE_NAME AS OBJECT_NAME, OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) AS OBJECT_DEFINITION, ROUTINE_TYPE AS OBJECT_TYPE FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE N'%' + @escapedSearchText + N'%' ESCAPE '\' 
	
	UNION ALL 
	
	SELECT TABLE_NAME AS OBJECT_NAME, OBJECT_DEFINITION(OBJECT_ID(TABLE_NAME)) AS OBJECT_DEFINITION, N'VIEW' AS OBJECT_TYPE FROM INFORMATION_SCHEMA.VIEWS 
	WHERE OBJECT_DEFINITION(OBJECT_ID(TABLE_NAME)) LIKE N'%' + @escapedSearchText + N'%' ESCAPE '\' 
	
END


GO