how do I select records that are like some string for any column in a table?

Question

I know that I can search for a term in one column in a table in t-sql by using like %termToFind%. And I know I can get all columns in a table with this:

SELECT * 
FROM MyDataBaseName.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = N'MyTableName`

How can I perform a like comprparison on each of the columns of a table? I have a very large table so I can't just spell out LIKE for each column.

Answer 1

As always, I'll suggest xml for this (I'd suggest JSON if SQL Server had native support for it :) ). You can try to use this query, though it could perform not so well on large number of rows:

;with cte as (
    select
        *,
        (select t.* for xml raw('data'), type) as data
    from test as t
)
select *
from cte
where data.exist('data/@*[local-name() != "id" and contains(., sql:variable("@search"))]') = 1

see sql fiddle demo for more detailed example.

Important note by Alexander Fedorenko in comments: it should be understood that contains function is case-sensitive and uses xQuery default Unicode code point collation for the string comparison.

More general way would be to use dynamic SQL solution:

declare @search nvarchar(max)
declare @stmt nvarchar(max)

select @stmt = isnull(@stmt + ' or ', '') + quotename(name) + ' like @search'
from sys.columns as c
where c.[object_id] = object_id('dbo.test')
--
-- also possible
--
-- select @stmt = isnull(@stmt + ' or ', '') + quotename(column_name) + ' like @search'
-- from INFORMATION_SCHEMA.COLUMNS
-- where TABLE_NAME = 'test'

select @stmt = 'select * from test where ' + @stmt

exec sp_executesql
    @stmt = @stmt,
    @params = N'@search nvarchar(max)',
    @search = @search

sql fiddle demo