How to drop all MySQL tables from the command-line?

Usually I open Terminal.app and connect to a remote MySQL database.

Then I use this command to drop a table:

mysql> drop table [table name];

But what I need is the command line to drop all tables in the database.

If I use:

mysql> drop database [database name];

I'll destroy the database completely and I won't be able to create tables again. Am I right?

1

3 Answers

You can drop the database then immediately recreate it:

mysql> drop database [database name];
mysql> create database [database name];

Or you could use a script to drop each table in the database.

5

You can try the following command:

mysqldump --no-data --add-drop-table DB_NAME | grep ^DROP | mysql -v DB_NAME

Or:

mysql --silent --skip-column-names -e "SHOW TABLES" DB_NAME | xargs -L1 -I% echo 'DROP TABLE `%`;' | mysql -v DB_NAME

Where DB_NAME is your database name. Database credentials you can specify either in ~/.my.cnf or adding them to the command (e.g. -uroot -proot).

This method has some advantages over dropping and creating the database in case your database user doesn't have permission to drop it.

3

mysql -u USERHERE -pPASSWORDHERE --silent --skip-column-names -e "SHOW TABLES" DATABASENAMEHERE | xargs -L1 -I% echo 'SET FOREIGN_KEY_CHECKS = 0; DROP TABLE%; SET FOREIGN_KEY_CHECKS = 1;' | mysql -u USERHERE -pPASSWORDHERE -v DATABASENAMEHERE

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy