How to select table name like '%apple%' and check which table contains some specific data.

Question

I want to check which table contains a specific values in all the table name in database ending with the word 'apple' eg: Greenapple

What i tried is
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%apple%'

I am getting all the tables ending with the
name apple from the above query but i want to search those tables which contains a given value with a condition.

Eg: select col1, col3, col6 FROM table_name_ending_with_apple WHERE ( col1=2 AND col3=7 AND col6=8)

Someone please help me. I am literally going blind googling

Answer 1

Okay upon consulting with someone, this is not possible without some somewhat complex query (of which is a much higher level than here on Sololearn). My only advice would be to start learning SQL querying for dynamic SQL

Answer 2

If I'm not mistaken, you can just use the AND keyword, so for the example:
WHERE TABLE_NAME LIKE '%apple%' AND col1=2 AND col3=7 AND col6=8

Answer 3

This *might* be what you're looking for:
https://stackoverflow.com/a/13085788

Answer 4

Akang Toshi that means there is an issue with your column referencing then, if given that error. You may have to specify which table those columns are in.

Answer 5

Akang Toshi if you don't want to go down the route of dynamic SQL, you could do a query to get the table names and then manually use those to query the tables you're looking for. Depending on how many tables you have that may or may not be a viable alternative.

Answer 6

Kamil Hamid What I meant was do a simple query that outputs the table names, then copy those names to a temporary file and write queries to query the tables manually one by one. Of course that is only a viable option if there is a limited number of tables. If there are a lot of tables (or if this is a task that has to be done several times) then figuring out how to do this automatically with dynamic SQL would be the better way to go. The link I posted above seems to be a query that does something similar.

Answer 7

Simon Sauter ah, my bad. Well thought!

Answer 8

Kamil Hamid i have tried that bro. It says invalid column name.

Answer 9

Kamil Hamid Yea I thought so. Anyway thanks for taking the trouble and answering and make my day a little simpler ☺️.

Answer 10

Simon Sauter that is dynamic sql