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?

Adjunct

Unless you have lots of other entities, why not just DROP DATABASE and start from scratch? – Mapping

To preserve user priveleges. – Adjunct

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) – Respondent

fyi if you happen to have phpMyAdmin installed it's easy to select all tables and drop them. – Foodstuff

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. – Drop

See also: How to drop all MySQL tables from the command-line?. – Deutoplasm

@Drop 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 ;-) – Reprisal

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. – Indecision

Answer 1

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

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.

Cartomancy

@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. – Respondent

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. – Proximo

Not convenient when there are hundreds of tables, but better than nothing if recreating database is not an option. – Coverley

@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. – Respondent

use the terminal if you are in linux or mac and it will work to login to your mysql account type : mysql -u <username> -p then hit enter now enter you password select database then write the above code to delete tables – Baumgartner

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. – Border

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? – Batavia

I've added an answer below that incorporates this solution into a shell script. – Evincive

Just to add my frustration: I consider the "In MySQL 5.7, CASCADE does nothing" approach to be a total blunder. – Fade

To use the output easier, use mysql -B when issuing SELECT above. – Tetany

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'; – Jennet

@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'; – Iroquoian

Answer 2

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

Respondent

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. – Duality

thanks helpful, and @Duality super helpful, should add an answer – Bulrush

Answer 3

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

Checkers

Works on tables, fails to delete views. Still reduced my typing massively :) thx. – Lutz

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. – Moseley

Take care of views ! SELECT table_name FROM information_schema.TABLES WHERE table_schema = SCHEMA() AND table_type="BASE TABLE"; – Blackstone

Thank you @Jean-Françoisand @Alexey! This has helped me almost a decade after you folks answered this! Kudos! – Faithless

Answer 4

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

Galloglass

why not just pipe mysqldump to mysql, without going through a file? – Impression

@Impression no particular reason. Knock yourself out. – Galloglass

Thanks, I already did, removed the parenthesis and piped mysqldump into grep into mysql, it works. Thanks again for your solution, it's good. – Impression

This one is missing the foreign key constraints; I have added a bash script here based on your answer: gist.github.com/cweinberger/c3f2882f42db8bef9e605f094392468f – Falchion

sooooooooooooo smart! – Bryce

nice! I'm using this in my script – Laryngeal

god saves you!!! I added your snippet with silent modifications here for docker guys gist.github.com/jrichardsz/… – Scad

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 – Gastronomy

Answer 5

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

Massengale

Short and clean solution. Way better than the procedure alternative. – Phionna

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; – Ravenna

See my answer for the adjustments: https://mcmap.net/q/73292/-mysql-drop-all-tables-ignoring-foreign-keys – Ravenna

Answer 6

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

Cistern

Nice, but it doesn't handle foreign keys unfortunately. – Proximo

Answer 7

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.

Kipton

this worked for me but, I had to replace -i with -I on macOS High Sierra – Harwood

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. – Kipton

Very useful, thank you! I had to pass a password using -pPASSWORD (no space between -p and PASSWORD) to both mysql commands – Celinacelinda

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. – Trencherman

Answer 8

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.

Clactonian

The above answer assumes that || is set to be the concatenation operator. More specifically, MySQL SQL mode contains PIPES_AS_CONCAT. Reference: dev.mysql.com/doc/refman/5.0/en/… – Belleslettres

@Ionut: cool! thanks for pointing that out. Fixed the code sample to use concat instead of || – Clactonian

Answer 9

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

Dissatisfied

Answer 10

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

Meaningful

Answer 11

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

Tanager

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! – Savour

Answer 12

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

Tisza

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. – Emotive

Answer 13

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.

Duckpin

Answer 14

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

Chericheria

Answer 15

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>

Phenacaine

This is an excellent reminder. – Beaverette

... except you also loose all users (with passwords), procs and views that had been set up too. – Honghonied

Answer 16

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.

Neal

Answer 17

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

Millburn

Answer 18

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

Leoraleos

Answer 19

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}

Evincive

Answer 20

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> 

Liturgy

Answer 21

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;

Ravenna

Answer 22

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!

Batwing

Useful but it don't really answers the question. – Suilmann

Answer 23

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

Grimonia

Answer 24

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"

Carbajal

Answer 25

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

Barred

Answer 26

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 ('|')

1. With "show tables;" query you will get the list of all tables;

2. Now copy the result and paste it into an excel sheet (assume all records are in column 'A' of excel)

3. 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),";")

4. Now create the final query list by using the CONCAT function

   =CONCAT("drop table ",C1)

Judsonjudus

Answer 27

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.

Dorsy

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. – Impression

Could you even explain where you clicked? Is there any tool involved for this? – Rickirickie

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. – Preiser