njudge njudge -4 years ago 42
SQL Question

Deleting dynamically managed tables in MySQL

I have a MySQL database that contains many tables, some of them created dynamically by the software. The dynamic tables have a consistent naming scheme:

dynamic_1
dynamic_2
...


However, I don't necessarily know how many of these tables there are.

I'd like to be able to delete all of these dynamic tables without dropping the entire database. I'd like to be able to do this purely in SQL. (Stored procedures are ok.) Is this is even possible?




Quick addendum.

The solution from Haim and Alexandre works great, but there's one special case we all missed. What if there are -no- dynamic tables? In that case
@v
will be NULL and we get an error when we try to execute. I added a second variable to handle this case:

SET @v = (SELECT CONCAT('drop table ', GROUP_CONCAT(a.table_name)) FROM information_schema.tables a where a.table_schema = DATABASE() AND a.table_name like 'dynamic_%');
SET @y = (SELECT IF (@V IS NOT NULL, @V, 'select 1'));
PREPARE s FROM @y;
EXECUTE s;

Answer Source

you can run this query and get all the sql queries that you need to run;

select concat( 'drop table ', a.table_name, ';' )
from information_schema.tables a 
where a.table_name like 'dynamic_%';

you can insert it to file like

INTO OUTFILE '/tmp/delete.sql';

update according to alexandre comment

SET @v = ( select concat( 'drop table ', group_concat(a.table_name))
    from information_schema.tables a 
    where a.table_name like 'dynamic_%'
    AND a.table_schema = DATABASE()
;);
 PREPARE s FROM @v; 
EXECUTE s;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download