Find a substring anywhere in a database.

I needed to find a pattern in a MSSQL 2008 database and I didn't know what table or column the data might be in. I was able to convert a script I found on StackOverflow.com over to a method that doesn't use server side cursors and returns the entire row for matches in any applicable columns to give the results context. Like the original poster's version, this assumes you are running it in the dbo role. If you intend to use this script with user input, you'll want to take extra care what gets assigned to @search_string. There's no protection against malicious input.

/* Based on http://stackoverflow.com/questions/591853/search-for-a-string-in-all-tables-rows-and-columns-of-a-db */
DECLARE
 @table_name     SYSNAME,
 @table_id       INT,
 @column_name    SYSNAME,
 @sql_string     VARCHAR(max),
 @lasttableid INT = 0,
 @lastcolumnid INT = 0,
 @search_string  VARCHAR(max) = 'test phrase'

declare @tables_cur table(entryid int not null identity(1,1), name sysname, object_id int)
declare @columns_cur table(column_id int not null, name sysname)

insert into @tables_cur (name, object_id)
select name, object_id
from sys.objects
where type='U'

while exists (select * from @tables_cur where entryid > @lasttableid) begin

 select top 1 @lasttableid = t.entryid, @table_name = t.name, @table_id = t.object_id
 from @tables_cur t
 where t.entryid > @lasttableid
 order by t.entryid

 delete from @columns_cur
 set @lastcolumnid = 0

 insert into @columns_cur (column_id, name)
 select column_id, name
 from sys.columns
 where object_id = @table_id
 and system_type_id in (167, 175, 231, 239)

 SET @sql_string = 'SELECT ''' + @table_name + ''' as [_TABLE_NAME_], * FROM [' + @table_name + '] where 1=0'

 while exists (select * from @columns_cur where column_id > @lastcolumnid) begin

  select top 1 @lastcolumnid = c.column_id, @column_name = c.name
  from @columns_cur c
  where c.column_id > @lastcolumnid
  order by c.column_id

  set @sql_string += ' or [' + @column_name + '] LIKE ''%' + @search_string + '%''';

 end
 print @sql_string
 EXECUTE('if exists (' + @sql_string + ') begin ' + @sql_string + ' end;')
end