Search all columns in all tables for a specific text

gistfile1.sql

set nocount on;

declare @TableName nvarchar(200)
declare @SchemaName nvarchar(200)
declare @ColumnName nvarchar(200)
declare @Sql nvarchar(4000)
declare @SearchValue nvarchar(200)
declare @Count int

-- Change the value below to the value to search for.
set @SearchValue = '%searchstring%'

-- Get a list of schema, table, column.
DECLARE Items

CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
select s.Name as SchemaName, t.name as TableName, c.name as ColumnName
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.columns c on t.object_id = c.object_id
where c.user_type_id in (167, 175, 239, 99, 35, 231)
OPEN Items
FETCH NEXT FROM Items
INTO @SchemaName, @TableName, @ColumnName

WHILE (@@FETCH_STATUS = 0)
BEGIN
 -- Execute a count(*) select in order to only return results with 1 or more hits.
 set @Sql = 'select @Count = count(*) from [' + @SchemaName + '].[' +  @TableName + '] where [' + @ColumnName + '] like ''' + @SearchValue + ''';'

 exec sp_executesql @sql, N'@Count int output', @Count output

 if @Count > 0
  begin
   -- Return result with tablename, hit column, * columns.
   set @Sql = 'select ''' + @TableName + ''' as TableName, ' + @ColumnName + ', *'
   set @Sql = @Sql + ' from [' + @SchemaName + '].[' + @TableName + ']'
   set @Sql = @Sql + ' where [' + @ColumnName + '] like ''' + @SearchValue + ''';'
   execute sp_executesql @sql
  end

  FETCH NEXT FROM Items
  INTO @SchemaName, @TableName, @ColumnName
END

CLOSE Items
DEALLOCATE Items