drop tables using regex - MySQL

Question

I have a bunch of tables in a MySQL database, some of them starting with phpbb_* which I wanted to delete all of them. Does someone know a way to do so instead of doing

drop table <tablename>;

every single time? Like a regex?

drop table phpbb*

or something like?

Maybe check this post: http://stackoverflow.com/questions/1589278/sql-deleting-tables-with-prefix — S.Visser, Oct 28 '12 at 15:43

score 16 · Accepted Answer · answered Oct 28 '12 at 15:49

You can use this MySQL procedure:

DELIMITER $$
CREATE PROCEDURE drop_tables_like(pattern VARCHAR(255), db VARCHAR(255))
BEGIN
    SELECT @str_sql:=CONCAT('drop table ', GROUP_CONCAT(table_name))
    FROM information_schema.tables
    WHERE table_schema=db AND table_name LIKE pattern;

    PREPARE stmt from @str_sql;
    EXECUTE stmt;
    DROP prepare stmt;
END$$

DELIMITER ;

For dropping all tables starting with 'a' in 'test1' database you can run:

CALL drop_tables_like('a%', 'test1');

Reference: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html

score 0 · Answer 2 · answered Jan 29 '18 at 18:58

Just to add to the above answer, MYSQL returns an error ERROR 1046 (3D000): No database selected if no database is selected before beginning the procedure. So prepend this statement use <<db_name>>; to the above answer. The revised solution would be:

use <<db_name>>;    
DELIMITER $$
CREATE PROCEDURE drop_tables_like(pattern VARCHAR(255), db VARCHAR(255))
BEGIN
    SELECT @str_sql:=CONCAT('drop table ', GROUP_CONCAT(table_name))
    FROM information_schema.tables
    WHERE table_schema=db AND table_name LIKE pattern;

    PREPARE stmt from @str_sql;
    EXECUTE stmt;
    DROP prepare stmt;
END$$    
DELIMITER ;
CALL drop_tables_like('a%', '<<db_name>>');

Make sure you replace <> with the actual name of the database. Cheers!

drop tables using regex - MySQL

2 Answers2