Certain DBALs and ORMs don’t provide a functional reset method to clear out schema cruft before rebuilding tables (I’m looking at you, Django). Others like SilverStripe tip toe around your schema, appending-never-deleting, so nothing ever gets flat out broken by a rebuild.
That behavior is OK with me because their scope usually doesn’t include schema and live data migration. That adds a lot of complexity (see Doctrine). But sometimes you just need to CTRL-Z the unholy mess that has become your table schema, without dropping the entire database.
MySQL doesn’t provide a DROP ALL TABLES FROM mydb; equivalent, so bring on the CLI:
$ mysqldump mydb --add-drop-table --no-data |grep ^DROP|mysql mydb
All regular options like -u and -p apply. Credit to http://www.thingy-ma-jig.co.uk/blog/10-10-2006/mysql-drop-all-tables for the snippet.
BONUS: Doing the same in PostgreSQL may not be as straight forward, as sequences and cascades can cause headaches. Here is a starting point I have used successfully: http://snipt.net/Fotinakis/drop-all-tables-from-postgresql-db-without-superuser/
$ psql -t mydb -c "SELECT 'DROP TABLE ' || n.nspname || '.' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)" | psql mydb
Happy deleting!
