Drop Tables with Wildcard

Databases
1

Hi

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’?

Regards,
Steve

2

query information_schema.tables to get the table names, then use the results to issue your drops

3

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…

4

Thanks Gents :slight_smile:

5

So I got


SELECT 
   CONCAT(
     "DROP TABLE ",  
      GROUP_CONCAT(TABLE_NAME)
    ) AS stmt 
    FROM information_schema.TABLES 
    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 :wink:

Regards,
Steve

6

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

7

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 :slight_smile:

Ok, I will go with what I have an not be lazy :wink:

Regards,
Steve