+ 2

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

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

22nd Apr 2022, 8:14 PM
Akang Toshi
Akang Toshi - avatar
10 Respuestas
+ 2
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
23rd Apr 2022, 11:54 AM
Kamil Hamid
Kamil Hamid - avatar
+ 1
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
22nd Apr 2022, 8:41 PM
Kamil Hamid
Kamil Hamid - avatar
+ 1
This *might* be what you're looking for: https://stackoverflow.com/a/13085788
22nd Apr 2022, 11:26 PM
Simon Sauter
Simon Sauter - avatar
+ 1
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.
23rd Apr 2022, 2:57 AM
Kamil Hamid
Kamil Hamid - avatar
+ 1
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.
23rd Apr 2022, 3:13 PM
Simon Sauter
Simon Sauter - avatar
+ 1
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.
23rd Apr 2022, 3:20 PM
Simon Sauter
Simon Sauter - avatar
+ 1
Simon Sauter ah, my bad. Well thought!
23rd Apr 2022, 3:22 PM
Kamil Hamid
Kamil Hamid - avatar
0
Kamil Hamid i have tried that bro. It says invalid column name.
22nd Apr 2022, 8:45 PM
Akang Toshi
Akang Toshi - avatar
0
Kamil Hamid Yea I thought so. Anyway thanks for taking the trouble and answering and make my day a little simpler ☺️.
23rd Apr 2022, 2:01 PM
Akang Toshi
Akang Toshi - avatar
0
Simon Sauter that is dynamic sql
23rd Apr 2022, 3:14 PM
Kamil Hamid
Kamil Hamid - avatar