# 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.

• 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.
• 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

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;



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.

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] \
grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \



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.

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];