MySQL Tip: Drop All Tables (With PostgreSQL Bonus)

MySQL Tip: Drop All Tables (With PostgreSQL Bonus)

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!

  • Brett Garland

    why would you just drop mydb and then re-create mydb since you’re droping all tables from it anyway?

    • dalesaurus

      Often it comes down to permissions or selective drops. If my db user doesn’t have CREATE rights or if I want to spare tables like Member or PasswordHash, I can just grep those out.