A quick question, does anyone know if there is a way to drop tables with a specific name + wildcard;
I would like to delete multiple tables that begin with ‘_temp’ like ‘__temp_atyztyj’ and ‘__temp_pqrtynr’ Is there any drop table command that I can drop both these tables without knowing what comes after ‘_temp’?
query information_schema.tables to get the table names, then use the results to issue your drops
You can use the catalog to generate the SQL DROP commands
SELECT 'DROP ' || [...]
and run the script after that.
EDIT: a split second too late…
So I got
"DROP TABLE ",
) AS stmt
WHERE TABLE_SCHEMA = "my_db" AND TABLE_NAME LIKE "__temp_%";
It generates a stmt like
DROP TABLE __temp_afsrpsy,__temp_dfsposb,__temp_exyrygx,__temp_gfyunsc,__temp_jhwshaj,__temp_khlmgnv,__temp_mhbhtfy,__temp_ondgdqr,__temp_oqucumc,__temp_prmgzax,__temp_qqhlwgi,__temp_vhrmdpu,__temp_xpqhrew,__temp_xraqrss,__temp_yvnplnu
This is OK as it is a two step process, one to generate the Drop statement and the other to copy and run this statement into the command line and run it; however is there a way to do this in one step in the command line or work_bench without using PHP?
Yes I’m curious and lazy
be aware that only mysql allows you to specify a list of tables in a single DROP statement
i really doubt it, but i’m forever surprised by what some people can come up with
Ok, I am testing a php object that in the end will do self cleanup of the temp table in question. I am not quite far enough along with it to drop the table as part of the routine as I still need to study the contents of the imported temp table so I will not be using this technique in production. I’ve used standard SQL for all SQL I use in the application and I don’t plan on implementing any dialect features unless I’m really need it. Thank you for point this out
Ok, I will go with what I have an not be lazy