Is there a way to run this query in all databases ? ( SQL Server 2008 R2 )
First, create a temporary table (not a table variable) to hold the results:

SET NOCOUNT ON;
GO
CREATE TABLE #results
(
  db SYSNAME,
  [schema] SYSNAME,
  [table] SYSNAME, 
  [column] SYSNAME,
  ColumnValue NVARCHAR(3640)
);
GO


Now, the following gets a little convoluted, because this is like peeling an onion. We need to build dynamic SQL commands that query directly against tables, based on the innermost scope (which is dynamic SQL against each database's metadata), and finally execute all of that for each database.

DECLARE @search NVARCHAR(100) = N'%foo%', -- N prefix is important!
  @sql          NVARCHAR(MAX) = N'',
  @cmd          NVARCHAR(MAX) = N'';

-- build a command per database
SELECT @sql += N'EXEC ' + QUOTENAME(name) 
  + '.sys.sp_executesql @cmd, N''@search NVARCHAR(MAX)'', @search;'
  FROM sys.databases 
  WHERE database_id &gt; 4 AND [state] = 0 AND user_access = 0 AND is_read_only = 0;

-- need an inner command to build a separate query
-- for every string-based column
SET @cmd = N'DECLARE @inner NVARCHAR(MAX) = N''''; 
  SELECT @inner += CHAR(10) + N''SELECT 
        [db]     = DB_NAME(),
        [schema] = N'''''' + s.name + '''''', 
        [table]  = N'''''' + t.name + '''''',
        [column] = N'''''' + c.name + '''''',
        ColumnValue = LEFT('' + QUOTENAME(c.name) + '',3640)
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @search;'' 
  FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239);

  EXEC sys.sp_executesql @inner, N''@search NVARCHAR(100)'', @search;';

-- now multi-nested actual execution
INSERT #Results(db,[schema],[table],[column],ColumnValue) 
  EXEC [master].sys.sp_executesql @sql, 
  N'@cmd NVARCHAR(MAX), @search NVARCHAR(100)', @cmd, @search;


Now, select the results and clean up:

SELECT db,[schema],[table],[column],ColumnValue 
  FROM #Results 
  ORDER BY db,[schema],[table],[column],ColumnValue;

DROP TABLE #Results;


This solution only deals with string-based columns; in your question you list int and decimal, but that strikes me as odd...