--change '%search string%' only

declare @num int, @count int, @exec nvarchar(500), @find nvarchar(50)

set @find =

declare @tab as table (TABLE_SCHEMA sysname,TABLE_NAME sysname,COLUMN_NAME sysname,Row_Number int primary key)

if (select OBJECT_ID('tempdb..#tab2')) is not null drop table #tab2

create table #tab2 (table_name sysname, colum_name sysname, column_value nvarchar(500))

insert into @tab

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME ,ROW_NUMBER() OVER(ORDER BY TABLE_NAME DESC) AS 'Row_Number'

from INFORMATION_SCHEMA.COLUMNS

where DATA_TYPE in ('varchar','nvarchar','char','nchar')

select @num=COUNT(*) from @tab

set @count = 1

while @count<=@num

begin

select @exec='set nocount on;INSERT INTO #tab2 SELECT ''' + TABLE_NAME + ''' as tab,''' + COLUMN_NAME + ''',['

+ COLUMN_NAME + '] FROM [' + TABLE_SCHEMA

+ '].[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''' + @find + '''' FROM @tab WHERE Row_Number=@count

--print @exec

execute (@exec)

set @count = @count +1

end

select * from #tab2