I need to drop all the tables in a database without knowing their names beforehand. The typical procedure is to drop and then recreate the database but this is not an option. What is the best way to do it?
There is a simpler bash one-liner using mysqldump (from Thingy Ma Jig Blog).
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
If you're getting this error:
ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails
Try the following:
(echo 'SET foreign_key_checks = 0;'; (mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP); echo 'SET foreign_key_checks = 1;') | \ mysql -u[USERNAME] -p[PASSWORD] -b [DATABASE]
Now it ignores the constraints.
You should use the information_schema tables to fetch the metadata about the database, and then drop the tables listed there.
There was an entry on the Xaprb blog recently which covers this nicely.
It includes why using
INFORMATION_SCHEMA isn't always a good thing and references a tool from those lovely people at Maatkit.
Try the following with
mk-find '<database>' --printf 'DROP TABLE %D.%N'
If you're happy with the results then:
mk-find '<database>' --exec 'DROP TABLE %D.%N'