Search text in all database objects

DECLARE @cmd                        varchar(1000),

            @search_string          varchar(200)

CREATE TABLE #temp(

      [Database_Name]         sysname,

      [Schema_Name]           sysname,

      [Object_Name]           sysname,

      [Object_Type]           nvarchar(60))

-- Set the search string

SET @search_string = 'gokul'

SET @cmd = 'INSERT INTO #temp SELECT DISTINCT ''?'', s.name AS Schema_Name, o.name AS Object_Name, o.type_desc FROM [?].sys.sql_modules m INNER JOIN [?].sys.objects o ON m.object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE m.definition Like ''%' + @search_string + '%'''

EXEC sp_msforeachdb @cmd

SELECT *

FROM #temp

ORDER BY [Database_Name], [Object_Name], [Object_Type]

DROP TABLE #temp

GO