SQL : Data Search Script - Version 3

version 3 :

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

/*
version 3 :    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

*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'searchstring'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
 and table_name not like 'sync%'
 and column_name <> 'order'
 and table_name in (select name from sys.sysobjects 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) +
 '  print ''' + table_schema + '.' + table_name  + '/' +column_name  + '''' + char(10) +
 ' end ' + char(10) +
 '' + char(10) +
 ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
 '''' + char(10)
from  information_schema.columns with (nolock)
where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
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) + 'go'