Drop All Tables in a MySQL Database

This is an excerpt from Adminbuntu, a site for Ubuntu Server administrators:

http://www.adminbuntu.com/drop_all_tables_in_a_database

IMPORTANT: This is a dangerous command. Back up the database first!

Drop all tables in a database, without dropping the database itself.

This creates a MySQL statement file that will drop all tables that begin with a certain string.

  • Replace USERNAMEHERE with the MySQL user to use
  • Replace PASSWORDHERE with the correct password
  • Replace DATABASENAMEHERE with the name of the database
mysql --user=USERNAMEHERE --password=USERNAMEHERE -BNe "SHOW TABLES" DATABASENAMEHERE | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=USERNAMEHERE --password=USERNAMEHERE DATABASENAMEHERE
Be Sociable, Share!