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

User · Answer

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 gt  SYSTEM CLEAR  mysql gt  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 gt  USE emall duplicate  Database changed mysql gt  SET FOREIGN KEY CHECKS   0                                                                                                                                                    Query OK  0 rows affected  0 00 sec      copy and paste generated query from step 1 mysql gt  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 gt  SET FOREIGN KEY CHECKS   1  Query OK  0 rows affected  0 00 sec   mysql gt  SHOW tables  Empty set  0 01 sec   mysql gt

User · Answer

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     gt    drop all tables sql mysql -u root -p database  lt    drop all tables sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

DB  your database name         amp  amp  mysql  DB  lt   SET FOREIGN KEY CHECKS 0         amp  amp  mysqldump --add-drop-table --no-data  DB   grep  DROP TABLE    grep -Ev        mysql  DB        amp  amp  mysql  DB  lt   SET FOREIGN KEY CHECKS 1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    gt  temp txt    mysqldump -u USER  -p PASSWORD  --add-drop-table --no-data  DATABASE    grep  DROP  gt  gt  temp txt    echo  SET FOREIGN KEY CHECKS   1    gt  gt  temp txt    mysql -u USER  -p PASSWORD   DATABASE   lt  temp txt    I know this is an old question  but I think this method is fast and simple

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

One-step solution without copying returned value from SQL Select query using procedure  SET FOREIGN KEY CHECKS   0  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 FOREIGN KEY CHECKS   1

User · Answer

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    gt    TMPFILE    CMD      gt  gt    TMPFILE   lt  lt  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    gt  gt    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      lt    TMPFILE  echo  Exit status is        rm   TMPFILE

User · Answer

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

User · Answer

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

User · Answer

This solution is based on  SkyLeach answer but with the support of dropping tables with foreign keys   echo  SET FOREIGN KEY CHECKS   0    gt    drop all tables sql mysqldump --add-drop-table --no-data -u user -p dbname   grep  DROP TABLE   gt  gt    drop all tables sql echo  SET FOREIGN KEY CHECKS   1    gt  gt    drop all tables sql mysql -u user -p dbname  lt    drop all tables sql

User · Answer

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

User · Answer

In php its as easy as    pdo   new PDO  mysql dbname YOURDB    root    root      pdo- gt 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- gt query  query  as  row         pdo- gt exec  row 0        pdo- gt exec  SET FOREIGN KEY CHECKS   1      Just remember to change YOURDB to the name of your database  and obviously the user pass

User · Answer

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

User · Answer

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  amp  amp  mysql -h host -u user -p password  dbName -e  SET FOREIGN KEY CHECKS   0  drop table  i   SET FOREIGN KEY CHECKS   1    done

User · Answer

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

[mysql] MySQL DROP all tables, ignoring foreign keys

Examples related to mysql

Examples related to foreign-keys

Examples related to innodb

Examples related to sql-drop