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

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

You can drop the database and then recreate it with the below -  mysql gt  drop database  database name   mysql gt  create database  database name

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to command-line

Examples related to sql-drop