How to check if a value exists in any of the columns in a table in sql

Question

Say, I have 100 columns in a table. I do not know in which columns a particular value could exist. So, I would like to check across all columns, if it exists in any of the 100 columns, I would like to select it.

I searched around a bit, and in most places the solution seems to be something like the following

select * 
from tablename 
where col1='myval' 
   or col2='myval' 
   or col3='myval' 
   or .. or col100='myval'

I also read a few forums where having to do this is said to be a bad case of database design, I agree, but I'm working on an already existing table in a database.

Is there a more intelligent way to do this?

Answer 1

One way is by reversing the In operator

select * 
from yourtable 
where 'Myval' in (col1,col2,col3,...)

If you don't want to manually type the columns use dynamic sql to generate the query

declare @sql varchar(max)='select * 
from yourtable 
where ''Myval'' in ('
select @sql+=quotename(column_name)+',' from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='yourtable'

select @sql =left(@sql,len(@sql)-1)+')'
--print @sql
exec sp_executesql @sql