How to remove all MySQL tables from the command-line without DROP database permissions?

The Question :

222 people think this question is useful

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.

The Question Comments :
  • A similar question has been asked before stackoverflow.com/questions/3476765/…
  • 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.
  • See also: How to drop all MySQL tables from the command-line?.
  • None of the linked question are the same as this one. This question is not duplicate!
  • 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

The Answer 1

312 people think this answer is useful

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; 

The Answer 2

275 people think this answer is useful

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.

The Answer 3

85 people think this answer is useful

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.

The Answer 4

47 people think this answer is useful

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

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

The Answer 5

6 people think this answer is useful

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’;”

Add a Comment