MySQL drop all tables from a database solution

The easiest solution to drop all tables from a database would be to use the DROP DATABASE statement to remove the database itself.

Once you remove the database, you can create it again using the CREATE DATABASE statement.

The following statement removes and creates a database named test_db:

DROP DATABASE test_db;

CREATE DATABASE test_db;

With that, you’ll have a fresh empty database with the same name as the one you dropped.

Mysql drop all tables using a shell script

But if you don’t want to recreate the database for some reason, then the next solution would be to use mysqldump command line tool to generate a set of DROP TABLE statements from your database.

Here’s the script from @cweinberger for dropping all tables while disabling foreign key checks:

#!/bin/bash

#usage: drop-all-tables -d database -u dbuser -p dbpass

TEMP_FILE_PATH='./drop_all_tables.sql'

while getopts d:u:p: option
do
	case "${option}"
	in
	d) DBNAME=${OPTARG};;
	u) DBUSER=${OPTARG};;
	p) DBPASS=${OPTARG};;
	esac
done

echo "SET FOREIGN_KEY_CHECKS = 0;" > $TEMP_FILE_PATH
( mysqldump --add-drop-table --no-data -u$DBUSER -p$DBPASS $DBNAME | grep 'DROP TABLE' ) >> $TEMP_FILE_PATH
echo "SET FOREIGN_KEY_CHECKS = 1;" >> $TEMP_FILE_PATH
mysql -u$DBUSER -p$DBPASS $DBNAME < $TEMP_FILE_PATH
rm -f $TEMP_FILE_PATH

You can save the file as drop_all_tables.sh and run it from the command line as follows:

sh drop-all-tables.sh -d [dbname] -u [dbuser] -p [dbpassword]

Replace [dbname], [dbuser], and [dbpassword] with your actual database name, user, and password.

Now all tables from your chosen database should be removed.

MySQL drop all tables from MySQL Workbench

Finally, if you’re using MySQL Workbench to manage your database, you follow these steps to drop all tables:

• Navigate to the Schemas tab
• Click on your database name, then click the Tables menu
• Click on the first table on the list, then hold SHIFT and click on the last table
• With all tables highlighted, right-click on one of the tables and select the drop tables option from the menu

Here’s a screenshot to help you find the option:

And those are the three ways you can drop all tables from a MySQL database.