SQL search all columns of a table for text value

Question

I have a huge table with many columns and I know that this columns sometimes takes some specific value 'MyValue'. How can I select all the rows in that one specific table containing 'MyValue' regardless in which column.

I found related topic here: How do I find a value anywhere in a SQL Server Database?

But my query needs a smaller cannon. I know the table name, it is, let's say 'MyTable'. I do not need to search the whole db.

Answer 1

You can do this by reversing the value and column in In operator.

SELECT *
FROM   Mytable
WHERE  'Myvalue' IN ( Col1, Col2, col3,.... ) 

If you don't want to type the columns, then pull it from information_schema.column view and create a dynamic query

Answer 2

You can concatenate all columns with + and then perform a LIKE search:

SELECT *
FROM   data
WHERE  col1 + '#' + col2 + '#' + col3 like '%test%'

Adding a separator (I use #) between the columns ensures you won't get false positives from the concatenation, e.g. if col2 = 'te' and col3 = 'st'

SQL Fiddle

Answer 3

To do it without a special procedure in a simple statement, you could convert each row to XML and then use an XQuery on the XML to search for any value in the row that matches. So for example:

declare @SearchValue as varchar(20)
set @SearchValue = 'MyValue'

select *
    --,(select MyTable.* for XML PATH (''),TYPE) AllColumns
    --,convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item')) FoundColumns
from MyTable
where convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item'))<>''

A procedure specifically designed for this task could probably do this more efficiently and could take advantage of indexes... etc. Honestly I would not put this into a production database solution without quite a bit of consideration, but as a throw together search tool it's not bad. I ran a search on a 700,000 record table in 40 seconds. However if I filter by each column individually it runs nearly instantly. Also a few more caveats:

• None of the table columns can not have spaces or other unfriendly characters for an XML tag. I couldn't figure out how to get column names with spaces to work. Maybe there's a way.
• The filter has to be written in XQuery... which is not exactly like SQL. But you can use =, <, >, and there's even pattern matching.
• The parameter for the query function must be a string literal. So you can't build a string dynamically. This is why I used the variable for your search values, but you could also use a sql:column("ColName") if needed.
• If searching for other types besides strings, the search string you use must match exactly what the field would be converted to as an XML value.