How to search multiple columns, in 1 or more tables, for a single string?

Q: How can I search all varchar columns in a given table for a specific string, without building the where clause for every field?

A: select * from table1 where val1 + val2 like '%xyz%'
/* where val1 and val2 are varchar columns */

Also, if you have full text indexes defined on the columns of a table you can use the contains clause to examine each column that is indexed. If all of the varchar columns in mytable are are included in the fulltext index, then following will return any row that has the string abc :

select * from mytable where contains(*, '"*abc*" )

Thanks to Pete F. for helping with this question.