CREATE PROCEDURE dbo.SearchAllDatabases
  @SearchTerm NVARCHAR(255) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  
  IF @SearchTerm IS NULL OR @SearchTerm NOT LIKE N'%[^%^_]%'
  BEGIN
    RAISERROR(N'Please enter a valid search term.', 11, 1);
    RETURN;
  END
 
  CREATE TABLE #results
  (
    [database]   SYSNAME,
    [schema]     SYSNAME,
    [table]      SYSNAME, 
    [column]     SYSNAME,
    ExampleValue NVARCHAR(1000)
  );

  DECLARE
    @DatabaseCommands  NVARCHAR(MAX) = N'', 
    @ColumnCommands    NVARCHAR(MAX) = N'';

  SELECT @DatabaseCommands = @DatabaseCommands + N'
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql 
        @ColumnCommands, N''@SearchTerm NVARCHAR(MAX)'', @SearchTerm;'
    FROM sys.databases 
    WHERE database_id  > 4  -- non-system databases  
      AND [state]      = 0  -- online 
      AND user_access  = 0; -- multi-user

    SET @ColumnCommands = N'DECLARE @q NCHAR(1),
          @SearchCommands NVARCHAR(MAX);
    
    SELECT @q = NCHAR(39), 
      @SearchCommands = N''DECLARE @VSearchTerm VARCHAR(255) = @SearchTerm;'';
  
    SELECT @SearchCommands = @SearchCommands + CHAR(10) + N''

      SELECT TOP (1)
        [db]     = DB_NAME(),
        [schema] = N'' + @q + s.name + @q + '', 
        [table]  = N'' + @q + t.name + @q + '',
        [column] = N'' + @q + c.name + @q + '',
        ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 1000) 
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE 
        WHEN c.system_type_id IN (35, 167, 175) THEN ''V'' 
        ELSE '''' END + ''SearchTerm;'' 
 
    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)
      AND c.max_length >= LEN(@SearchTerm);

    PRINT @SearchCommands;
    EXEC sys.sp_executesql @SearchCommands, 
      N''@SearchTerm NVARCHAR(255)'', @SearchTerm;';
  
  INSERT #Results
  (
    [database],
    [schema],
    [table],
    [column],
    ExampleValue
  )
  EXEC [master].sys.sp_executesql @DatabaseCommands, 
    N'@ColumnCommands NVARCHAR(MAX), @SearchTerm NVARCHAR(255)', 
    @ColumnCommands, @SearchTerm;

  SELECT [Searched for] = @SearchTerm;
  
  SELECT [database],[schema],[table],[column],ExampleValue 
    FROM #Results 
    ORDER BY [database],[schema],[table],[column];
END
GO

-- generate an error because this is not a sensible search:
EXEC dbo.SearchAllDatabases @SearchTerm = N'%';
GO

-- search for columns containing "http://"
EXEC dbo.SearchAllDatabases @SearchTerm = N'%http://%';

-- search for columns containing "%" or "_":
EXEC dbo.SearchAllDatabases @SearchTerm = N'%[%]%';
EXEC dbo.SearchAllDatabases @SearchTerm = N'%[_]%';

-- search for columns containing "update" followed by "dbo.":
EXEC dbo.SearchAllDatabases @SearchTerm = N'%update%dbo.%';