Didn't like the solution. There is an error in it (already noted in a previous post). Added to that, I have a method that also searches views. The extra search of the syscomments.text was applied for very long procedures.

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;