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.
This question is related to
mysql
command-line
sql-drop
You can drop the database and then recreate it with the below:-
mysql> drop database [database name];
mysql> create database [database name];
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.
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 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';"
Source: Stackoverflow.com