create table dbo.tmpTestTable(a nvarchar(50),b nvarchar(10),c int,d float)

Insert into dbo.tmpTestTable select 'a1f','2a',3,4

Insert into dbo.tmpTestTable select 'a2ff','a1',3,4

Insert into dbo.tmpTestTable select 'a3fff','a2',1,4

Insert into dbo.tmpTestTable select 'a4fff','2a',3,1

Insert into dbo.tmpTestTable select 'a5fff','a2',3,4

select * from dbo.tmpTestTable

declare @Search nvarchar(50)

set @Search='4'

declare @sql nvarchar(max)

--run through all the columns

select @sql=coalesce(@sql+'or ['+[name]+'] like ''%'+@Search+'%'' ',' ['+[name]+'] like ''%'+@Search+'%'' ')

from sys.columns where object_id=Object_id('[dbo].[tmpTestTable]') --you can filter on certain types

select @sql='Select * from [dbo].[tmpTestTable] where '+@sql

print @sql

exec sp_executesql @sql

Personally I would search for each column separate! Then you can add the results separate and add which column is reponsible.