Generate SQL to Search All Columns in a Table

If you are searching for a given value in a table with numerous columns not knowing which column could match the value, you'll find it tedious to write as many OR clauses as columns in your table.

Here is a simple T-SQL script to generate SQL code with all the column names and the OR clauses.

 declare @sql nvarchar(max);
 select @sql = 'select * from MY_TABLE_NAME where ';
 select @sql = @sql + c.name + ' = ''value'' or '
 from sys.tables t 
  inner join sys.columns c on t.object_id = c.object_id 
  and c.system_type_id in(231,167) --this restricts target columns to just nvarchar,varchar types
  and t.name = 'MY_TABLE_NAME';
  
 select @sql = left(@sql,len(@sql)-3)+';'; -- remove the last or
 print @sql;

MY_TABLE_NAME has to be changed to your specific table of course. This script can be improved to add conditional casting. When the column type (system_type_id) is string then no casting is needed but when integer then convert to varchar and when the type is datetime for example then use convert(varhcar(10),101) for example.