dynamic - In SQL, how to find all rows where ANY column meets a condition? -

dynamic - In SQL, how to find all rows where ANY column meets a condition? -

i want find rows in table column meets condition.

for example, if have table robtest:

-- illustration table create table #robtest (a varchar(10), b varchar(10), c varchar(10)) insert #robtest select 'blue', 'green', 'green' union select 'green', 'blue', 'green' union select 'green', 'green', 'green'

if wanted find rows column has field value 'blue', write:

select * #robtest = 'blue' or b = 'blue' or c = 'blue'

and obtain rows 1 , 2. solution unweildy if table had dozens of column names or if can't sure column names might alter , running query audit, instance. how dynamically?

we can utilize information_schema.columns find column names, utilize cursor iterate on columns, using dynamic sql populate temporary table rows satisfying condition.

following code above, assuming #robtest definition given in question:

-- find columns of #robtest select column_name #columnnames tempdb.information_schema.columns table_name '%robtest%' -- create place hold our results select * #robtemp #robtest truncate table #robtemp -- set varaibles our iteration process declare @sql nvarchar(1000) declare @columncursor cursor declare @currentcolumn nvarchar(100) set @columncursor = cursor select column_name #columnnames -- go through columns 1 @ time , run query on column. open @columncursor fetch next @columncursor @currentcolumn while @@fetch_status = 0 begin set @sql = 'select * #robtest ' + @currentcolumn + ' = ''blue''' insert #robtemp exec sp_executesql @sql fetch next @columncursor @currentcolumn end -- show results select * #robtemp

sql dynamic