Sometimes you just have to start over. I had a database that had a bunch of tables in it, almost 9000. The problem was that I didn’t know if they were the right tables. I had a mysqldump files that did have the right tables, but I didn’t want any extras around. So I wanted to start over with the fresh import. In MySQL you would normally just do a drop database x; create database x; and you are done. However, I didn’t have a user with the rights to create a database.
With a small database you can do a command line like
SELECT group_concat(table_name order by table_name desc separator ‘ ‘) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ‘DATABASENAME’ ;
and then type drop table and paste in the resulting table list. This does not work with anything more than a dozen or so tables. This question gets to be very tricky when you are dealing with a LARGE database.
I found an article from back in 2006 that had a rather unique idea and I’ve expanded it to be faster and to work better with extremely large database. You will still have to wait a bit for completion, but when you need to remove a large number of tables from a database, try this command line:
mysqldump -u[USERNAME] -p[PASSWORD] [DATABASE]–add-drop-table –no-data –skip-lock-tables | grep ^DROP | mysql -A -u[USERNAME] -p[PASSWORD] [DATABASE]
MySQL dump will create a list of drop commands for every table in the database, the grep will isolate those and feed them back into mysql for execution. It’s a rather slick solution and while you may still have to wait for 5 minutes (or even much more) as all the tables are listed and dropped, it will get the job done.