Drop All Tables

Since there exists neither DROP TABLE * nor DROP TABLE WHERE name LIKE ’something’, here are three ways to achieve the desired result:

For version 5.0+:

   mysql -N -e "SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') FROM
   information_schema.tables WHERE table_schema = 'dbName'" | mysql dbName

For any version, but requires grep:

   mysqldump --add-drop-table -d dbName | grep -e "DROP TABLE" | mysql dbName

You can alter the WHERE clause of the first or the grep of the second to support whatever specifics you like, such as all the tables that have a common prefix.

And a stored procedure:

     – drop tables that match a given pattern
     drop procedure if exists drop_tables_like;
     delimiter $
     create procedure drop_tables_like(tblpattern varchar(100))
     begin
     declare tblcount int default 0;
     declare tblname varchar(100);
     declare done int default 0;
     declare curs cursor for select table_name from
     information_schema.tables where table_name like tblpattern
     and table_schema = ‘test’;
     declare continue handler for sqlstate ‘02000′ set done = 1;
     open curs;
     myloop: loop
     fetch curs into tblname;
     if done then close curs; leave myloop; end if;
     set @dropstmt = concat(”drop table `”, tblname, “`”);
     prepare stmt from @dropstmt;
     execute stmt;
     deallocate prepare stmt;
     set tblcount = tblcount + 1;
     end loop;
     select tblcount as “Tables Deleted”;
     end $
     delimiter ;