MySQL DROP all tables, ignoring foreign keys

Question

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

Unless you have lots of other entities, why not just DROP DATABASE and start from scratch? — StuartLC, Aug 13 '10 at 12:31
I just realized that in the meanwhile you got an answer by Dion Truter that is more complete than mine and suggest to accept that instead. (the "drop *all* tables" part is not covered by mine) — chiccodoro, May 03 '12 at 07:24
fyi if you happen to have phpMyAdmin installed it's easy to select all tables and drop them. — User, Nov 22 '12 at 02:22
That is true but only for phpMyAdmin in version 4.x. If you select all tables and choose `Drop` from dropdown menu you can uncheck `Foreign key check` checkbox. — jmarceli, Sep 13 '13 at 13:39
See also: [How to drop all MySQL tables from the command-line?](http://superuser.com/questions/308071/drop-all-table-in-mysql-database-using-terminal). — kenorb, Oct 27 '14 at 11:29
@jmarceli For PMA prior to version 4: Select all tables from PMA, choose DROP. It will list the DROP statements. Copy them. Open the SQL execution tab of PMA. First paste `SET FOREIGN_KEY_CHECKS = 0` then *the drop statements you copied* and lasty `SET FOREIGN_KEY_CHECKS = 1` and run ;-) — Lashae, Feb 15 '18 at 06:07
User privileges are not dropped in MySQL (anymore?) when a DB is dropped, so it's certainly easier and faster now to just drop the DB, as long as you have CREATE/DROP DB privileges. — dualed, Apr 11 '18 at 15:12

score 709 · Accepted Answer · edited Mar 05 '20 at 04:34

709

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

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 1: 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.

Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME` statement to DROP VIEW `VIEW_NAME` manually.

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

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.

edited Mar 05 '20 at 04:34

izogfif

6,000
2
35
25

answered Nov 23 '11 at 19:54

Dion Truter

7,592
1
19
9

8

@Timmm: I have written 3 drops in my answer - however, this does not execute them either. You have to copy them from Stackoverflow and paste them in your MySQL Workbench or whereever. With the select above you get all matching drops "for free". You just have to copy-paste them. – chiccodoro Nov 15 '12 at 09:24
4

Yeah I know, but I wanted to use it in a script. What I ended up actually doing is `DROP DATABASE foo; CREATE DATABASE foo;`, which isn't quite the same but worked for me. – Timmmm Nov 15 '12 at 09:44
2

Not convenient when there are hundreds of tables, but better than nothing if recreating database is not an option. – Nicolas Raoul Apr 16 '13 at 05:58
3

@Timmmm: Looking at this again - you are right, it doesn't fully answer the question. Jean's answer looks promising - it does both auto-generate the DROP statements and execute them, while properly applying the SET FOREIGN_KEY_CHECKS before and after. – chiccodoro Jan 13 '14 at 15:05
1

use the terminal if you are in linux or mac and it will work to login to your mysql account type : mysql -u -p then hit enter now enter you password select database then write the above code to delete tables – ismnoiet Mar 30 '15 at 09:25
1

I like that this doesn't actually run the actual drop so you get a chance to see the commands before it actually happens which is really dangerous if you love your job. – JohnnyQ Jan 18 '17 at 05:33
1

Using the terminal creates entries inbetween `|` simbols... si there a way to not pretty print and export the result directly to a file called results.sql which one would then source or stdin? – Ray Foss Jan 27 '17 at 21:01
I've added an answer below that incorporates this solution into a shell script. – Philip Callender Mar 07 '17 at 02:06
Just to add my frustration: I consider the "In MySQL 5.7, CASCADE does nothing" approach to be a total blunder. – Tuncay Göncüoğlu Mar 31 '17 at 13:58
1

To use the output easier, use mysql -B when issuing SELECT above. – No-Bugs Hare Jan 31 '18 at 06:17
You can easily filter out views using table_type = 'BASE TABLE': `SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;') FROM information_schema.tables WHERE table_schema = 'MyDatabaseName' and table_type = 'BASE TABLE';` – jfx Oct 09 '20 at 11:05
@diontruter I like your answer, but I generally add a small tweak add a group_concat and it makes it easier to copy the commands in one chunk: `SELECT group_concat(concat('DROP TABLE IF EXISTS `', table_name, '`;') separator '') FROM information_schema.tables WHERE table_schema = 'MyDatabaseName';` – Kevin Finkenbinder Oct 11 '22 at 13:09

chiccodoro · Answer 2 · 2014-08-18T06:19:05.257

161

From http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys:

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists customers;
drop table if exists orders;
drop table if exists order_details;
SET FOREIGN_KEY_CHECKS = 1;

(Note that this answers how to disable foreign key checks in order to be able to drop the tables in arbitrary order. It does not answer how to automatically generate drop-table statements for all existing tables and execute them in a single script. Jean's answer does.)

edited Aug 18 '14 at 06:19

answered Aug 13 '10 at 12:33

chiccodoro

14,407
19
87
130

17

If you use MySQL Workbench, you can avoid having to type all the table names by selecting all tables in the left column, right-clicking, then 'drop tables' option. IT will generate the SQL which you can copy and paste between the SET FOREGIN_KEY_CHECKS statement's - probably similar in other GUI's as well. – chris Apr 27 '12 at 18:47
thanks helpful, and @chris super helpful, should add an answer – Andrew Dec 07 '17 at 16:52

score 128 · Answer 3 · edited Mar 22 '13 at 08:22

128

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`;

edited Mar 22 '13 at 08:22

Alexey Morozov

1,259
10
12

answered Oct 16 '12 at 15:09

Jean-François Beauchamp

5,485
8
43
77

2

Works on tables, fails to delete views. Still reduced my typing massively :) thx. – chrisinmtown Aug 03 '16 at 16:32
1

7 years later I'd say you should wrap `_tableName` with a backquotes. Otherwise it will fail on some tables like `group` or other keywords. – sashaaero Dec 25 '19 at 11:54
Take care of views ! SELECT table_name FROM information_schema.TABLES WHERE table_schema = SCHEMA() AND table_type="BASE TABLE"; – Glaubule Dec 27 '19 at 15:50
Thank you @Jean-Françoisand @Alexey! This has helped me almost a decade after you folks answered this! Kudos! – theHeman Mar 16 '23 at 09:01

score 73 · Answer 4 · answered Jan 15 '16 at 19:59

73

every approach above includes a lot more work than this one AFAICT...

( mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' ) > ./drop_all_tables.sql
mysql -u root -p database < ./drop_all_tables.sql

answered Jan 15 '16 at 19:59

SkyLeach

1,363
13
15

2

why not just pipe `mysqldump` to `mysql`, without going through a file? – Rolf May 28 '18 at 16:40
3

@Rolf no particular reason. Knock yourself out. – SkyLeach May 29 '18 at 04:29
3

Thanks, I already did, removed the parenthesis and piped `mysqldump` into `grep` into `mysql`, it works. Thanks again for your solution, it's good. – Rolf May 30 '18 at 12:49
8

This one is missing the foreign key constraints; I have added a bash script here based on your answer: https://gist.github.com/cweinberger/c3f2882f42db8bef9e605f094392468f – cweinberger Jun 06 '18 at 12:45
sooooooooooooo smart! – zx1986 Jun 23 '18 at 04:25
nice! I'm using this in my script – satnam Oct 21 '18 at 18:22
god saves you!!! I added your snippet with silent modifications here for docker guys https://gist.github.com/jrichardsz/1552426802d46e0f7dd06b9dde1101c1#improve-docker – JRichardsz Aug 04 '22 at 16:15
You can add support for foreign keys with simple `--init-command` option, i.e.: `mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' | mysql -u root -p --init-command="SET FOREIGN_KEY_CHECKS = 0;" database` – Kepi Oct 14 '22 at 19:31

score 41 · Answer 5 · edited May 23 '17 at 12:26

From this answer,

execute:

  use `dbName`; --your db name here
  SET FOREIGN_KEY_CHECKS = 0; 
  SET @tables = NULL;
  SET GROUP_CONCAT_MAX_LEN=32768;

  SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
  FROM   information_schema.tables 
  WHERE  table_schema = (SELECT DATABASE());
  SELECT IFNULL(@tables, '') 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 drops tables from the database currently in use. You can set current database using use.

Or otherwise, Dion's accepted answer is simpler, except you need to execute it twice, first to get the query, and second to execute the query. I provided some silly back-ticks to escape special characters in db and table names.

  SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
  FROM   information_schema.tables
  WHERE  table_schema = 'dbName'; --your db name here

Short and clean solution. Way better than the procedure alternative. — Rafael Renan Pacheco, Jul 23 '19 at 18:32
This doesn't work if there are no tables. Replace the lines SELECT IFNULL(...) and SET @tables = CONCAT(...) with SELECT IF(@tables IS NULL, 'SELECT NULL FROM (SELECT NULL) AS `empty` WHERE 0=1', CONCAT('DROP TABLE IF EXISTS ', @tables)) INTO @tables; — Mirco Babin, Apr 20 '23 at 12:10
See my answer for the adjustments: https://stackoverflow.com/a/76064028/2746339 — Mirco Babin, Apr 20 '23 at 12:26

score 20 · Answer 6 · answered Apr 15 '12 at 07:00

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`;

Nice, but it doesn't handle foreign keys unfortunately. – Timmmm Oct 08 '12 at 12:53 — Timmmm, Oct 08 '12 at 12:53

Pablo Santa Cruz · Answer 7 · 2010-08-13T12:40:13.273

13

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.

edited Aug 13 '10 at 12:40

answered Aug 13 '10 at 12:33

Pablo Santa Cruz

176,835
32
241
292

The above answer assumes that `||` is set to be the concatenation operator. More specifically, MySQL SQL mode contains `PIPES_AS_CONCAT`. Reference: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_pipes_as_concat – Ionuț G. Stan Aug 13 '10 at 12:37
@Ionut: cool! thanks for pointing that out. Fixed the code sample to use `concat` instead of `||` – Pablo Santa Cruz Aug 13 '10 at 12:40

mgershen · Answer 8 · 2018-01-01T12:24:13.470

13

A one liner to drop all tables from a given database:

echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"

Running this will drop all tables from database DATABASE_NAME.

And a nice thing about this is that the database name is only written explicitly once.

edited Jan 01 '18 at 12:24

answered Aug 07 '16 at 13:07

mgershen

1,567
16
22

1

this worked for me but, I had to replace -i with -I on macOS High Sierra – Ali Selcuk Jan 01 '18 at 11:53
1

Thanks @AliSelcuk . Both -i and -I works for me on Ubuntu, so I'll change it to -I in order for this to work for macOS as well. – mgershen Jan 01 '18 at 12:23
1

Very useful, thank you! I had to pass a password using -pPASSWORD (no space between -p and PASSWORD) to both mysql commands – Mike Feb 22 '19 at 11:11
1

Important to also add -uUSER and -pPASS after both mysql commands. Very useful to add in scripts to empty a database before an import. Saved my day. – Oliver M Grech Feb 24 '22 at 07:56

score 13 · Answer 9 · edited Mar 06 '21 at 17:00

One-step solution without copying returned value from SQL Select query using procedure.

SET FOREIGN_KEY_CHECKS = 0;
SET SESSION group_concat_max_len = 1000000;

SET @TABLES = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @TABLES FROM information_schema.tables 
  WHERE table_schema = 'databaseName';

SET @TABLES = CONCAT('DROP TABLE IF EXISTS ', @TABLES);

PREPARE stmt FROM @TABLES;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET SESSION group_concat_max_len = 1024;
SET FOREIGN_KEY_CHECKS = 1

score 12 · Answer 10 · answered Jan 15 '18 at 12:29

Googling on topic always brings me to this SO question so here is working mysql code that deletes BOTH tables and views:

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_sql1 = CONCAT('DROP TABLE IF EXISTS ', _tableName);
        SET @stmt_sql2 = CONCAT('DROP VIEW IF EXISTS ', _tableName);

        PREPARE stmt1 FROM @stmt_sql1;
        PREPARE stmt2 FROM @stmt_sql2;

        EXECUTE stmt1;
        EXECUTE stmt2;

        DEALLOCATE PREPARE stmt1;
        DEALLOCATE PREPARE stmt2;
    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`;

score 10 · Answer 11 · answered Jul 24 '13 at 01:18

I came up with this modification on Dion Truter's answer to make it easier with many tables:

SET GROUP_CONCAT_MAX_LEN = 10000000;
SELECT CONCAT('SET FOREIGN_KEY_CHECKS=0;\n', 
              GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`')
                           SEPARATOR ';\n'),
              ';\nSET FOREIGN_KEY_CHECKS=1;')
FROM information_schema.tables
WHERE table_schema = 'SchemaName';

This returns the entire thing in one field, so you can copy once and delete all the tables (use Copy Field Content (unquoted) in Workbench). If you have a LOT of tables, you may hit some limits on GROUP_CONCAT(). If so, increase the max len variable (and max_allowed_packet, if necessary).

SET GROUP_CONCAT_MAX_LEN was the trick I needed. I had a script that was working, but always failed the first time with some truncated table name, and then successfully finished when run a second time. Thanks! — dualmon, May 07 '14 at 00:44

kfox · Answer 12 · 2020-02-03T04:52:11.027

9

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 -h$host -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do  echo $i && mysql -h$host -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done

edited Feb 03 '20 at 04:52

answered Apr 09 '13 at 20:33

kfox

1,176
13
16

Really like this solution but I don't see how the host variable is used. It doesn't seem to be used in any mysql call in the for loop. – Matthew Zackschewski Jan 27 '20 at 13:59

score 8 · Answer 13 · answered Nov 11 '14 at 18:17

If in linux (or any other system that support piping, echo and grep) you can do it with one line:

echo "SET FOREIGN_KEY_CHECKS = 0;" > temp.txt; \
mysqldump -u[USER] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP >> temp.txt; \
echo "SET FOREIGN_KEY_CHECKS = 1;" >> temp.txt; \
mysql -u[USER] -p[PASSWORD] [DATABASE] < temp.txt;

I know this is an old question, but I think this method is fast and simple.

score 6 · Answer 14 · answered Feb 04 '20 at 17:15

Drop all the tables from database with a single line from command line:

mysqldump -u [user_name] -p[password] -h [host_name] --add-drop-table --no-data [database_name] | grep ^DROP | mysql -u [user_name] -p[password] -h [host_name] [database_name]

Where [user_name], [password], [host_name] and [database_name] have to be replaced with a real data (user, password, host name, database name).

score 4 · Answer 15 · answered Mar 01 '22 at 08:10

4

Just a soft reminder,

If possible & have no other issues, you can drop the database and recreate it.

drop database <database_name>
create database <database_name>

answered Mar 01 '22 at 08:10

sh6210

4,190
1
37
27

This is an excellent reminder. – tremby Apr 26 '22 at 18:55
4

... except you also loose all users (with passwords), procs and views that had been set up too. – user1432181 Sep 14 '22 at 12:35

score 3 · Answer 16 · answered Aug 21 '13 at 13:38

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.

artfulrobot · Answer 17 · 2014-07-08T10:27:58.917

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

score 3 · Answer 18 · answered Jun 24 '18 at 11:14

Just put here some useful comment made by Jonathan Watt to drop all tables

MYSQL="mysql -h HOST -u USERNAME -pPASSWORD DB_NAME"
$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL
unset MYSQL

It helps me and I hope it could be useful

score 2 · Answer 19 · answered Mar 07 '17 at 02:05

Building on the answer by @Dion Truter and @Wade Williams, the following shell script will drop all tables, after first showing what it is about to run, and giving you a chance to abort using Ctrl-C.

#!/bin/bash

DB_HOST=xxx
DB_USERNAME=xxx
DB_PASSWORD=xxx
DB_NAME=xxx

CMD="mysql -sN -h ${DB_HOST} -u ${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME}"

# Generate the drop statements
TMPFILE=/tmp/drop-${RANDOM}.sql
echo 'SET FOREIGN_KEY_CHECKS = 0;' > ${TMPFILE}
${CMD} $@ >> ${TMPFILE} << ENDD
    SELECT concat('DROP TABLE IF EXISTS \`', table_name, '\`;')
    FROM information_schema.tables
    WHERE table_schema = '${DB_NAME}';
ENDD
echo 'SET FOREIGN_KEY_CHECKS = 1;' >> ${TMPFILE}

# Warn what we are about to do
echo
cat ${TMPFILE}
echo
echo "Press ENTER to proceed (or Ctrl-C to abort)."
read

# Run the SQL
echo "Dropping tables..."
${CMD} $@ < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}

score 2 · Answer 20 · answered Mar 05 '19 at 17:09

Simple and clear (may be).

Might not be a fancy solution, but this worked me and saved my day.

Worked for Server version: 5.6.38 MySQL Community Server (GPL)

Steps I followed:

 1. generate drop query using concat and group_concat.
 2. use database
 3. disable key constraint check
 4. copy the query generated from step 1
 5. enable key constraint check
 6. run show table

MySQL shell

mysql> SYSTEM CLEAR;
mysql> SELECT CONCAT('DROP TABLE IF EXISTS `', GROUP_CONCAT(table_name SEPARATOR '`, `'), '`;') AS dropquery FROM information_schema.tables WHERE table_schema = 'emall_duplicate';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dropquery                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE emall_duplicate;
Database changed
mysql> SET FOREIGN_KEY_CHECKS = 0;                                                                                                                                                   Query OK, 0 rows affected (0.00 sec)

// copy and paste generated query from step 1
mysql> DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`;
Query OK, 0 rows affected (0.18 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW tables;
Empty set (0.01 sec)

mysql>

score 1 · Answer 21 · answered Aug 20 '15 at 14:07

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!

Useful but it don't really answers the question. – jrosell Sep 21 '16 at 12:12 — jrosell, Sep 21 '16 at 12:12

score 1 · Answer 22 · answered Apr 20 '23 at 12:25

A litte refinement of Nawfal excellent answer with the following adjustments:

no error if the database has no tables.
save and restore the FOREIGN_KEY_CHECKS setting.
GROUP_CONCAT explicit SEPARATOR for easier code reading.
use name dropTablesStmt, because the stmt name is to global and might overwrite some other already prepared statement named stmt.

use `database`;

SET @tables = NULL;
SET GROUP_CONCAT_MAX_LEN=1048576;

SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`' SEPARATOR ',') INTO @tables
FROM   information_schema.tables
WHERE  table_schema = (SELECT DATABASE());

SELECT     IF(@tables IS NULL, 
              'SELECT NULL FROM (SELECT NULL) AS `empty` WHERE 0=1', 
              CONCAT('DROP TABLE IF EXISTS ', @tables)) INTO @tables;
PREPARE    dropTablesStmt FROM @tables;

SELECT     @@FOREIGN_KEY_CHECKS INTO @SAVED_FOREIGN_KEY_CHECKS;
SET        FOREIGN_KEY_CHECKS = 0;

EXECUTE    dropTablesStmt;

SET        FOREIGN_KEY_CHECKS = @SAVED_FOREIGN_KEY_CHECKS;

DEALLOCATE PREPARE dropTablesStmt;

score 0 · Answer 23 · answered Apr 04 '18 at 15:15

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

score 0 · Answer 24 · answered Sep 14 '18 at 09:28

0

DB="your database name" \
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=0" \
    && mysqldump --add-drop-table --no-data $DB | grep 'DROP TABLE' | grep -Ev "^$" | mysql $DB \
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=1"

answered Sep 14 '18 at 09:28

Ernestas Stankevičius

2,420
2
24
30

score -1 · Answer 25 · answered Feb 23 '15 at 09:05

I use the following with a MSSQL server:

if (DB_NAME() = 'YOUR_DATABASE') 
begin
    while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
    begin
         declare @sql nvarchar(2000)
         SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
         FROM information_schema.table_constraints
         WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
         exec (@sql)
         PRINT @sql
    end

    while(exists(select 1 from INFORMATION_SCHEMA.TABLES))
    begin
         declare @sql2 nvarchar(2000)
         SELECT TOP 1 @sql2=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
         FROM INFORMATION_SCHEMA.TABLES
        exec (@sql2)
        PRINT @sql2
    end
end
else
    print('Only run this script on the development server!!!!')

Replace YOUR_DATABASE with the name of your database or remove the entire IF statement (I like the added safety).

score -1 · Answer 26 · edited Feb 05 '22 at 07:09

All have given good answers, however, I have an alternative option for users familiar with spreadsheet/excel sheets. As per the first solution we get a list of commands but we still need to truncate the first and last characters ('|')

With "show tables;" query you will get the list of all tables;
Now copy the result and paste it into an excel sheet (assume all records are in column 'A' of excel)
At first you need to delete the first and last '|' symbol-function to delete the first character ie. '|'

=RIGHT(A1,LEN(A1)-1)

function to delete the last character ie. '|' and add an ending semicolon

=CONCAT(LEFT(B1,LEN(B1)-1),";")

Now create the final query list by using the CONCAT function

=CONCAT("drop table ",C1)

score -8 · Answer 27 · answered Mar 17 '16 at 14:08

-8

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.

answered Mar 17 '16 at 14:08

azza idz

623
3
13
27

It almost worked but I clicked the green button before the blue button and I got a box to the left and I clicked cancel but then I drank my coffee and forgot about it. – Rolf Jun 04 '18 at 07:54
3

Could you even explain where you clicked? Is there any tool involved for this? – Nico Haase Sep 14 '18 at 10:49
Azza, you forgot to mention the name of the application you refer to. Edit your answer and include it, so future people will know and maybe upvote your answer. – Jose Manuel Abarca Rodríguez Jul 27 '21 at 17:06

MySQL DROP all tables, ignoring foreign keys

27 Answers27

Linked

Related