MySQL DROP all tables, ignoring foreign keys

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

 Answers

I found the generated set of drop statements useful, and recommend these tweaks:

1. Limit the generated drops to your database like this:

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

Note: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them.

2. Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:

"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."

Therefore, in order for the drop statements to work if you need:

SET FOREIGN_KEY_CHECKS = 0

This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with

SET FOREIGN_KEY_CHECKS = 1

3. The final execution should look like:

SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

NB: to use output of SELECT easier, mysql -B option can help.

---

Here is SurlyDre's stored procedure modified so that foreign keys are ignored:

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

---

Here's a cursor based solution. Kinda lengthy but works as a single SQL batch:

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;

END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

---

You can do:

select concat('drop table if exists ', table_name, ' cascade;')
  from information_schema.tables;

Then run the generated queries. They will drop every single table on the current database.

Here is some help on drop table command.

---

Here is an automated way to do this via a bash script:

host=$1
dbName=$2
user=$3
password=$4

if [ -z "$1" ]
then
    host="localhost"
fi

# drop all the tables in the database
for i in `mysql -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do  echo $i && mysql -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done

---

In php its as easy as:

$pdo = new PDO('mysql:dbname=YOURDB', 'root', 'root');

$pdo->exec('SET FOREIGN_KEY_CHECKS = 0');

$query = "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
          FROM information_schema.tables
          WHERE table_schema = 'YOURDB'";

foreach($pdo->query($query) as $row) {
    $pdo->exec($row[0]);
}

$pdo->exec('SET FOREIGN_KEY_CHECKS = 1');

Just remember to change YOURDB to the name of your database, and obviously the user/pass.

---

In a Linux shell like bash/zsh:

DATABASE_TO_EMPTY="your_db_name";
{ echo "SET FOREIGN_KEY_CHECKS = 0;" ; \
  mysql "$DATABASE_TO_EMPTY" --skip-column-names -e \
  "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') \
   FROM information_schema.tables WHERE table_schema = '$DATABASE_TO_EMPTY';";\
  } | mysql "$DATABASE_TO_EMPTY"

This will generate the commands, then immediately pipe them to a 2nd client instance which will delete the tables.

The clever bit is of course copied from other answers here - I just wanted a copy-and-pasteable one-liner (ish) to actually do the job the OP wanted.

Note of course you'll have to put your credentials in (twice) in these mysql commands, too, unless you have a very low security setup. (or you could alias your mysql command to include your creds.)

---

This is a pretty old post, but none of the answers here really answered the question in my opinion, so I hope my post will help people!

I found this solution on another question that works really well for me:

mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table \`$table\`" DB_NAME; done

That will actually empty all your tables in the database DB_NAME, and not only display the TRUNCATE command line.

Hope this helps!

---

This solution is based on @SkyLeach answer but with the support of dropping tables with foreign keys.

echo "SET FOREIGN_KEY_CHECKS = 0;" > ./drop_all_tables.sql
mysqldump --add-drop-table --no-data -u user -p dbname | grep 'DROP TABLE' >> ./drop_all_tables.sql
echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./drop_all_tables.sql
mysql -u user -p dbname < ./drop_all_tables.sql

---

Best solution for me so far

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts. After choosing objects in set Scripting option click Advanced Button. Under "Script DROP and CREATE" select Script DROP.

Run script.

Drop multiple MySQL tables

Last week I looked at how to drop a table with MySQL showing how to do this with a query and then using the phpMyAdmin web based interface. This time I will show how to drop multiple tables with a single query and then how to do the same with phpMyAdmin.

SQL Query

Dropping multiple tables is the same as dropping a single table; additional tables are added to the DROP TABLE query in comma separated fashion. Note that running the following query to delete the example categories, products and orders table is irrecoverable and there is no prompting.

DROP TABLE categories, orders, products;

Using phpMyAdmin

To drop multiple tables in phpMyAdmin select the page which shows all the tables for a database. Tick the boxes for the tables you want to delete, and select "Drop" from the "With selected:" drop down box. This is shown in the screenshot below where all three tables have been checked: categories, orders and products.

The page will submit and a confirmation page will display asking if you really do want to drop those tables. Click "Yes" and they will be dropped.

Related posts:

• MySQL: SQL to drop a column from a table
• How to drop a table with MySQL