SQL : Data Search Script - Version 5

version 5 :        1) copes with schemas other than dbo!
                        2) restrict to tables only (was doing views!)
                        3) nolock table hint added
                        4) uniqueidentifier added to data types searched
                        5) relays progress to window instantly using raiserror
(http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

/*
DATA SEARCH SCRIPT

version 5 :  
   1) copes with schemas other than dbo!
   2) restrict to tables only (was doing views!)
   3) nolock table hint added
   4) uniqueidentifier added to data types searched
   5) relays progress to window instantly using raiserror (http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

YOU MUST RUN THIS IN GRID VIEW!!! 
*/

declare @columncount int
declare @searchdata varchar(2550)
-- set string to search for here
set @searchdata = 'doering'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock)  where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_schema + '].[' + table_name + 
  '] with (nolock) where [' + 
  column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
  '  raiserror( ''' + table_schema + '.' + table_name  + '/' +column_name  + ''', 10, 1) WITH NOWAIT' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' +
  ''', 10, 1) WITH NOWAIT' + char(10)
 from  information_schema.columns with (nolock)
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10)