Are looking for dynamic column search i.e. want to pass column name as variable where the value "test" should be searched in? If that is the case, you will need dynamic sql. see http://www.sommarskog.se/dyn-search.html
A small algo that I can think of if you are ready to accept a procedure to do the above task:
1. Get the list of columns in the particular table(from syscolumns) 3. Search the above colums one by one (while searching you may need to covert all the columns to VARCHAR if you use LIKE in your search query).
Someone correct me if this is not possible. Thanks
you can also download an open source tool called Nautilus (http://sourceforge.net/projects/nautilus/). 1-find the table, you don't need the exact name, just part of it. 2-put the value you want to find and the tool will build the query for you. You can get the result or the query. The query is not the same if you put a numeric value or a string value ! guess why ?
3-finally, you can also find any value in any table...