Fresh Blurbs

How to Delete All Tables in a MySQL Database

If you are working on a system with somewhat large number of tables, you probably have wanted to delete a number of tables matching a pattern (dropping all tables is a specific case of this one). You can do that, by creating a very simple stored procedure:

mysql> delimiter $$
create procedure drop_tables_like(pattern varchar(255))
begin

SELECT 
@drop_sql:=concat('DROP TABLE IF EXISTS ', group_concat(table_name)) 
drop_statement
FROM information_schema.tables
WHERE table_schema=database() and table_name like pattern;

IF (@drop_sql IS NOT NULL) THEN
  PREPARE stmt from @drop_sql;
  EXECUTE stmt;
  DROP PREPARE stmt;
END IF;

end$$
delimiter ;

then you can use this procedure to mass-delete tables like:

mysql> call drop_tables_like ('ika%');
comments powered by Disqus