Search in every column

I'm building an abstract gem. i need a sql query that looks like this

SELECT * FROM my_table WHERE * LIKE '%my_开发者_开发技巧search%'

is that possible?

edit: I don't care about querys performance because it's a feature function of a admin panel, which is used once a month. I also don't know what columns the table has because it's so abstract. Sure i could use some rails ActiveRecord functions to find all the columns but i hoped to avoid adding this logic and just using the *. It's going to be a gem, and i can't know what db is going to be used with it. Maybe there is a sexy rails function that helps me out here.

---

As I understand the question, basically you are trying to build a sql statement which should check for a condition across all columns in that table. A dirty hack, but this generates the required Sql.

condition_string = MyTable.column_names.join(' LIKE ? OR ')
MyTable.all(:conditions => [condition_string, '%my_search%'])

However, this is not tested. This might work.

---

* LIKE '...' isn't valid according to the SQL standards, and not supported by any RDBMS I'm aware of. You could try using a function like CONCAT to make the left argument of LIKE, though performance won't be good. As for SELECT *, it's generally something to be avoided.

---

No, SQL does not support that syntax.

To search all columns you need to use procedures or dynamic SQL. Here's another SO question which may help:

SQL: search for a string in every varchar column in a database

EDIT: Sorry, the question I linked to is looking for a field name, not the data, but it might help you write some dynamically SQL to build the query you need.

You didn't say which database you are using, as there might be a vendor specific solution.

---

Its only an Idea, but i think it worth testing!

It depends on your DB you can get all Columns of a table, in MSSQL for example you can use somethink like:

select name from syscolumns where id=object_id('Tablename')

Under Oracle guess its like:

select column_name from USER_TAB_COLUMNS where TABLE_NAME = 'Tablename'

and then you will have to go through these columns usign a procedure and maby a cursor so you can check for each Column if the data your searching for is in there:

if ((select count(*) from Tablename where Colname = 'searchingdata') > 0) 
then keep the results in a separated table(ColnameWhereFound, RecNrWhereFound).

The matter of Datatye may be an Issue if you try to compare strings with numbers, but if you notice for instance under SQL-Server the syscolumns table contains a column called "usertype" which contains a number seems to refer to the Datatype stored in the Columne, like 2 means string and 7 means int, and 2 means smallint, guess Oracle would have something similar too.

Hope this helps.