Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

How delete all tables from MySQL database?

Connect to the target database. Select all tables from the left sidebar. Right-click and choose delete, or simply hit delete button. Press Cmd + S to commit changes to the server.

What is the command to remove all tables in database?

The easiest solution to drop all tables from a database would be to use the DROP DATABASE statement to remove the database itself. Once you remove the database, you can create it again using the CREATE DATABASE statement.

Which MySQL command is used to remove all records from a table in MySQL?

The MySQL DELETE Statement The DELETE statement is used to delete existing records in a table.

Which command in MySQL can be used to erase the entire database?

To do delete a database you need the command 'DROP DATABASE'. The syntax is similar to creating a database. 'DROP DATABASE <name>;', where <name> is the name of the database you want to delete.


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


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

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

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';"