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

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.