DECLARE @SQL NVARCHAR(MAX) -- Parameter to sp_MSforeachdb
-- Parameters into @SQL
DECLARE @SearchText varchar(100) = '<Place the String Here>'
SET @SearchText = '%' + @SearchText + '%'

IF OBJECT_ID('TEMPDB..##Report') IS NOT NULL
BEGIN
	DROP TABLE ##Report;-- Drop the temp table if already exists
END
CREATE TABLE ##Report(
db VARCHAR(100),
[DB Object]  VARCHAR(100),
[DB Object Type]  VARCHAR(100),
[Definition] NVARCHAR(MAX),
create_date DATETIME,
modify_date DATETIME
)

SET QUOTED_IDENTIFIER OFF; 
SET @SQL = REPLACE("
USE [?]; 
DECLARE @db VARCHAR(100) = '?';

INSERT INTO ##Report
SELECT @db [db],* 
FROM (


SELECT * FROM (
SELECT DISTINCT ROUTINE_SCHEMA + '.' + ROUTINE_NAME [DB Object], ROUTINE_TYPE AS [DB Object Type], ROUTINE_DEFINITION, o.create_date, o.modify_date
FROM INFORMATION_SCHEMA.ROUTINES i
INNER JOIN (SELECT o.name, c.text [ROUTINE_TEXT], o.create_date, o.modify_date
			FROM sys.syscomments c 
			INNER JOIN sys.objects o on c.id = o.object_id) O 
ON i.ROUTINE_NAME = O.name
WHERE PatIndex('@SearchText',ROUTINE_DEFINITION) + PatIndex('@SearchText',ROUTINE_TEXT) > 0
UNION ALL
SELECT TABLE_SCHEMA + '.' + TABLE_NAME  [DB Object], 'VIEW' AS [DB Object Type], VIEW_DEFINITION, o.create_date, o.modify_date
FROM INFORMATION_SCHEMA.VIEWS i
INNER JOIN (SELECT o.name, c.text [ROUTINE_TEXT], o.create_date, o.modify_date
			FROM sys.syscomments c 
			INNER JOIN sys.objects o on c.id = o.object_id) O 
ON i.TABLE_NAME = O.name
WHERE PatIndex('@SearchText',VIEW_DEFINITION) > 0
) A
) B
ORDER BY 1,3,2


","@SearchText",@SearchText)
SET QUOTED_IDENTIFIER ON;

BEGIN TRY
EXEC sp_MSforeachdb @SQL;
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
	PRINT @SQL
END CATCH

SELECT * FROM ##Report ORDER BY db,modify_date desc,  [DB Object Type],[DB Object];
DROP TABLE ##Report;

select
   object_name(object_id) as Name
from
   sys.sql_modules
where
   definition like '%your_string_comes_here%'
order by
   Name

CREATE FUNCTION dbo.fn_objects_text_search( 
  @object_name varchar(128),
  @searched_text varchar(100)
) RETURNS int
AS
BEGIN
RETURN (
  SELECT 
    -- Return 1 if any match found, 0 else ----
    CASE WHEN EXISTS( 
      -- Mark matches by block ----
      SELECT 1
      FROM 
      (
        SELECT
          CASE 
          -- Found in the first block ----
          WHEN text1 LIKE '%' + @searched_text + '%' THEN 1
          -- Found in the second block ----
          WHEN text2 LIKE '%' + @searched_text + '%' THEN 1
          -- Found in the boundary ----
          WHEN RIGHT( text1, LEN( @searched_text ) ) + LEFT( text2, LEN( @searched_text ) ) LIKE '%' + @searched_text + '%' THEN 1
          ELSE 0 
          END AS found
        FROM (
          -- Get first and second 4000 nchar blocks in the same row ----
          SELECT 
            c.text as text1, 
            isnull( c2.text, '' ) AS text2
          FROM    
            syscomments c 
          INNER JOIN 
            sysobjects o 
          ON 
            c.id = o.id
          INNER JOIN
            syscomments c2
          ON 
            c.id = c2.id AND c2.colid = c.colid + 1 
          WHERE
            name = @object_name
        ) AS a
      ) AS b
      WHERE found <> 0      
    )
    THEN 1
    ELSE 0
    END AS found
)
END