How to search in all columns of a table

Question

I would like to know how I can search a substring in all columns of a table when I do not know the names of the columns? Is there a foreach-loop-functionallity I do not know?

you can't. you need to specify columns to match against, e.g. `select * from yourtable where * LIKE '%foo%'` is not going to work. — Marc B, Apr 10 '14 at 16:00
because I am lazy and I just want to find some strings in a Database I dont know very well — user3518571, Apr 10 '14 at 16:00
@Silver89 How do I loop through columns without knowing the names — user3518571, Apr 10 '14 at 16:01
but you know the name of the table? Get the column names from information schema and write a stored procedure that'll do the work for you. If you don't know the name of the table, you can get them from information schema too ... — VMai, Apr 10 '14 at 16:02
*"...without knowing the names"* --- Sounds and smells like a hackjob to me. *"If it looks like a duck, and it quacks like a duck, then it's a duck."* — Funk Forty Niner, Apr 10 '14 at 16:02
@Fred-ii- No not a hack-job. I just need to search in a big Database. Sure, I could look up the names but this would be very hard because there are a lot columns.. — user3518571, Apr 10 '14 at 16:04
Reading through your reply should tell you why it was downvoted. For reference: `*because I am lazy* and I just want to find some strings in a Database I dont know very well ` — aaron-bond, Apr 10 '14 at 16:04
How can you **not** know the column names? You'll need to do some pretty fancy explanation to get out of this one. — Funk Forty Niner, Apr 10 '14 at 16:05
Yeah.. I know the names but I do not want to write a giant query for a simple search... oh man.. — user3518571, Apr 10 '14 at 16:06
Oh guys... what is wrong with you. Developers are lazy and instead of providing a helpful answer you downvote a good question. — user3518571, Apr 10 '14 at 16:12
All you needed to do was give a more concise explanation. You know that, and I know that (*now*). If you want to find something and pop out some results, then that will depend on what the query should look like. Do provide some form of an example in your question in order to take the guesswork out of it. @user3518571 FYI: I did not downvote. — Funk Forty Niner, Apr 10 '14 at 16:15

score 1 · Accepted Answer · edited Apr 10 '14 at 16:18

1

If I understand you correctly then this may work, brute forces a search through all the results.

Here is a PDO-based example:

$stmt = $dbh->prepare("SELECT * FROM table");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $row){   
    foreach($row as $column){

        if(strpos($column, "find me") !== false)
            echo $match."found in:".$column."<br />";;
    }

}

edited Apr 10 '14 at 16:18

Funk Forty Niner

74,450
15
68
141

answered Apr 10 '14 at 16:05

Dan

11,914
14
49
112

Thanks, but isn´t there a more elegant way? – user3518571 Apr 10 '14 at 16:16

score 1 · Answer 2 · answered Apr 10 '14 at 16:23

I am also lazy. I am also a ColdFusion programmer, not php. However programming logic is the same no matter what the language. I would do this:

Step 1 - Run this query and output the results somewhere

select * 
from mytable
where 1 = 2

The outputted results will include the field names. Copy and paste then into your source code as a comment. Delete the ones that you are not going to query. Convert the remaining ones to a list variable. In ColdFusion, that list would look something like this:

listOfFields = "field2,field3,field8,etc";

Fields 1,4,5,6, and 7 were intentionally excluded. We are then going to loop through the list in our query. In ColdFusion, this would be the syntax

<cfquery>
select somefields
from sometables
where 1 = 2
<cfloop list="#listOfFields#" index = "thisField">
or #thisField# = something
</cfloop>

This meets the laziness criteria because you only have to get the field names once. Whether it's better or worse than getting the columns from the system tables and looping through them depends. Doing it this way will make your own app run faster because you don't have to query the system tables every time. However, if a new column is added to your table, you'll have to modify your source code to include it in your search.

If you do decide to query the system tables, make sure you only select char and varchar fields.

score 1 · Answer 3 · edited May 23 '17 at 12:21

Adapt the sample query given at https://stackoverflow.com/a/1054988/1967396

SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

to get the names of the columns. Then loop over those names to search the table one column at a time for the specific strings. This takes advantage of the efficiency of SQL search and prevents you making a copy of the entire database just to search it (slowly) with nested foreach loops (as in @Silver89's answer).

How to search in all columns of a table

3 Answers3