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

This question already has an answer here:

• MySQL DROP all tables, ignoring foreign keys 

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.

 Answers

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; 

---

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://.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';"