237

How do I drop all tables in Windows MySQL, using command prompt? The reason I want to do this is that our user has access to the database drops, but no access to re-creating the database itself, for this reason we must drop the tables manually. Is there a way to drop all the tables at once? Bear in mind that most of the tables are linked with foreign keys so they would have to be dropped in a specific order.

Improve this question
7
  • 3
    A similar question has been asked before stackoverflow.com/questions/3476765/… Commented Sep 13, 2012 at 10:00
  • 3
    It seems OP is asking to drop all tables from the command prompt, not from MySQL (as it's on the linked question), so in my opinion it's not duplicate. Commented Oct 27, 2014 at 11:42
  • 1
    See also: How to drop all MySQL tables from the command-line?. Commented Oct 27, 2014 at 11:42
  • 3
    None of the linked question are the same as this one. This question is not duplicate! Commented Nov 16, 2014 at 17:00
  • 1
    Asked the question 2 years ago, and still think it's not a duplicate one, mainly because most of the other answers simply drop the database and re-create it. However it was specifically said that one does not want to drop the database itself Commented Nov 27, 2014 at 14:48

5 Answers 5

Reset to default
334

You can generate statement like this: DROP TABLE t1, t2, t3, ... and then use prepared statements to execute it:

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'database_name'; -- specify DB name here.

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Well this needs a bit of tweaking as it cannot drop the tables that are constrained by keys
This works fine if we SET FOREIGN_KEY_CHECKS = 0; and do SET FOREIGN_KEY_CHECKS = 1; after deletion this will allow us to delete all tables without having key checks;
NB It may also be necessary to increase GROUP_CONCAT_MAX_LEN and to escape the schema and name with GROUP_CONCAT('`', table_schema, '`.`', table_name, '`').
CONCAT "DROP TABLE " must includes " IF EXIST ", this has been reduced my time.
This is not the best solution. @Kostonos fixed an important issue with this command and edited Devart's answer, but it was not accepted. Please see Kostanos' answer on this page. (stackoverflow.com/a/18625545/114558)
|
295

The @Devart's version is correct, but here are some improvements to avoid having error. I've edited the @Devart's answer, but it was not accepted.

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

This script will not raise error with NULL result in case when you already deleted all tables in the database by adding at least one nonexistent - "dummy" table.

And it fixed in case when you have many tables.

And This small change to drop all view exist in the Database

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @views = NULL;
SELECT GROUP_CONCAT('`', TABLE_NAME, '`') INTO @views
  FROM information_schema.views
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@views,'dummy') INTO @views;

SET @views = CONCAT('DROP VIEW IF EXISTS ', @views);
PREPARE stmt FROM @views;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

It assumes that you run the script from Database you want to delete. Or run this before:

USE REPLACE_WITH_DATABASE_NAME_YOU_WANT_TO_DELETE;

Thank you to Steve Horvath to discover the issue with backticks.

Improve this answer

15 Comments

Actually, it does raise an error.
Could you be more specific please? Which error did you get?
for the overly cautious, SELECT @tables\G after the last SELECT above will give you a listing of what's about to be dropped - look over it before executing the second half of the snippet
@SteveHorvath thank you to pointing the issue with backticks. I fixed the query and it runs perfectly now with (group, select and other tables).
This is the proper answer
|
92

Try this.

This works even for tables with constraints (foreign key relationships). Alternatively you can just drop the database and recreate, but you may not have the necessary permissions to do that.

mysqldump -u[USERNAME] -p[PASSWORD] \
  --add-drop-table --no-data [DATABASE] | \
  grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \
  mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

In order to overcome foreign key check effects, add show table at the end of the generated script and run many times until the show table command results in an empty set.

Improve this answer

3 Comments

Wish I could vote this up twice. So simple and plays really nicely with environments like AWS Beanstalk where everything comes in from env vars anyway. Just mysqldump -h$RDS_HOSTNAME -u$RDS_USERNAME -p$RDS_PASSWORD --add-drop-table --no-data $RDS_DB_NAME | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -h$RDS_HOSTNAME -u$RDS_USERNAME -p$RDS_PASSWORD $RDS_DB_NAME
A very neat solution
Love the idea. Here's the same thing for all database a user have access to: mysqldump --host=127.0.0.1 --all-databases --user=$mysql_user --password=$mysql_password --add-drop-table --no-data | grep -e '^DROP \| FOREIGN_KEY_CHECKS\|USE' | mysql --host=127.0.0.1 --user=$mysql_user --password=$mysql_password
50

You can drop the database and then recreate it with the below:-

mysql> drop database [database name];
mysql> create database [database name];
Improve this answer

6 Comments

As I said the user has no permissions to create the database, all we can do is create,update,drop tables
This solution also is not correct, in case if you have some particular configuration for your database, like encoding, permissions etc..
Dangerous to use. You can also loose your database settings that you forgot to check...
Easiest alternative ever.
can use 'show create database database_name' before running this
|
6

The accepted answer does not work for databases that have large numbers of tables, e.g. Drupal databases. Instead, see the script here: https://stackoverflow.com/a/12917793/1507877 which does work on MySQL 5.5. CAUTION: Around line 11, there is a "WHERE table_schema = SCHEMA();" This should instead be "WHERE table_schema = 'INSERT NAME OF DB INTO WHICH IMPORT WILL OCCUR';"

Improve this answer

1 Comment

By the way, if doing Drupal development, the really easy way to do this is with the drush command line tool. The command is: drush sql-drop

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.