Truncate all tables in a MySQL database in one command

Question

Is there a query  command  to truncate all the tables in a database in one operation   I want to know if I can do this with one single query

User · Answer

Use phpMyAdmin in this way      Database View    Check All  tables     Empty    If you want to ignore foreign key checks  you can uncheck the box that says          Enable foreign key checks   You ll need to be running atleast version 4 5 0 or higher to get this checkbox   Its not MySQL CLI-fu  but hey  it works

User · Answer

We can write a bash script like below truncate tables in mysql         type mysql  gt  dev null 2 gt  amp 1  amp  amp  echo  quot MySQL present  quot     sudo apt-get install -y mysql-client          tables   mysql -h 127 0 0 1 -P  MYSQL PORT -u  MYSQL USER  -p MYSQL PASSWORD -e  quot USE  BACKEND DATABASE      SHOW TABLES  quot       tables list   tables           query string  quot USE  BACKEND DATABASE  SET FOREIGN KEY CHECKS   0  quot      for table in  quot   tables list    1  quot      do         query string  quot  query string TRUNCATE TABLE    table     quot      done     query string  quot  query string SET FOREIGN KEY CHECKS   1  quot           mysql -h 127 0 0 1 -P  MYSQL PORT -u  MYSQL USER -p MYSQL PASSWORD -e  quot  query string quot     You can replace env variables with your MySQL details  Using one command you can truncate all the tables in a DB

User · Answer

lt  php    connect to database  conn mysqli connect  localhost   user   password   database        check connection if  mysqli connect errno        exit  Connect failed     mysqli connect error           sql query  sql  mysqli query  conn  TRUNCATE     TABLE NAME        Print message if   sql     TRUE      echo  data delete successfully     else    echo  Error      conn- gt error      conn- gt close       gt    Here is code snippet which I use to clear a table  Just change  conn info and TABLE NAME

User · Answer

This worked for me  Change database  username and password accordingly  mysql -Nse  show tables  -D DATABASE -uUSER -pPWD   while read table  do echo  quot SET FOREIGN KEY CHECKS   0 drop table    table   SET FOREIGN KEY CHECKS   1  quot   done   mysql DATABASE -uUSER -pPWD

User · Answer

Here is my variant to have  one statement to truncate  em all    First  I am using a separate database named  util  for my helper stored procedures  The code of my stored procedure to truncate all tables is   DROP PROCEDURE IF EXISTS trunctables  DELIMITER    CREATE  PROCEDURE trunctables theDb varchar 64   BEGIN     declare tname varchar 64       declare tcursor CURSOR FOR      SELECT table name FROM information schema tables WHERE table type  lt  gt   VIEW  AND table schema   theDb      SET FOREIGN KEY CHECKS   0       OPEN tcursor      l1  LOOP         FETCH tcursor INTO tname          if tname   NULL then leave l1  end if          set  sql   CONCAT  truncate     theDB         tname                PREPARE stmt from  sql          EXECUTE stmt          DEALLOCATE PREPARE stmt      END LOOP l1      CLOSE tcursor      SET FOREIGN KEY CHECKS   1   END    DELIMITER     Once you have this stored procedure in your util database  you can call it like  call util trunctables  nameofdatabase      which is now exactly one statement  -

User · Answer

No  There is no single command to truncate all mysql tables at once  You will have to create a small script to truncate the tables one by one   ref  http   dev mysql com doc refman 5 0 en truncate-table html

User · Answer

mysqldump -u root -p --no-data dbname  gt  schema sql mysqldump -u root -p drop dbname mysqldump -u root -p  lt  schema sql

User · Answer

I know this isn t exactly one command  but the desired result can be achieved from within phpMyAdmin by following these steps    Select  all  tables to be removed  Check All  Select  Drop     Truncate  from the  With selected   list On the confirmation page   Do you really want to    copy the query  everything with the red background  Go at the top and click on SQL and write   SET FOREIGN KEY CHECKS 0   then paste the previously copied query Click  Go    The idea is to quickly get all the tables from the database  which you do in 5 seconds and 2 clicks  but disable foreign key checks first  No CLI and no dropping the database and adding it again

User · Answer

The following MySQL query will itself produce a single query that will truncate all tables in a given database  It bypasses FOREIGN keys   SELECT CONCAT            SET FOREIGN KEY CHECKS 0              GROUP CONCAT dropTableSql SEPARATOR                        SET FOREIGN KEY CHECKS 1            as dropAllTablesSql FROM     SELECT  Concat  TRUNCATE TABLE    table schema       TABLE NAME  AS dropTableSql          FROM    INFORMATION SCHEMA TABLES          WHERE   table schema    DATABASE NAME    as queries

User · Answer

I find that TRUNCATE TABLE    has trouble with foreign key constraints  even after a NOCHECK CONSTRAINT ALL  so I use a DELETE FROM statement instead  This does mean that identity seeds are not reset  you could always add a DBCC CHECKIDENT to achieve this   I Use the code below to print out to the message window the sql for truncating all the tables in the database  before running it  It just makes it a bit harder to make a mistake   EXEC sp MSforeachtable  PRINT   ALTER TABLE   NOCHECK CONSTRAINT ALL    EXEC sp MSforeachtable  print   DELETE FROM      EXEC sp MSforeachtable  print   ALTER TABLE   WITH CHECK CHECK CONSTRAINT all

User · Answer

An idea could be to just drop and recreate the tables   EDIT    Jonathan Leffler  True  Other Suggestion  or case you dont need to truncate ALL tables    Why not just create a basic stored procedure to truncate specific tables  CREATE PROCEDURE  dbo   proc TruncateTables  AS TRUNCATE TABLE Table1 TRUNCATE TABLE Table2 TRUNCATE TABLE Table3 GO

User · Answer

MS SQL Server 2005    Remove PRINT for actual execution      EXEC sp MSforeachtable  PRINT   TRUNCATE TABLE        If your database platform supports INFORMATION SCHEMA views  take the results of the following query and execute them   SELECT  TRUNCATE TABLE     TABLE NAME FROM INFORMATION SCHEMA TABLES   Try this for MySQL   SELECT Concat  TRUNCATE TABLE    TABLE NAME  FROM INFORMATION SCHEMA TABLES   Adding a semicolon to the Concat makes it easier to use e g  from within mysql workbench   SELECT Concat  TRUNCATE TABLE    TABLE NAME       FROM INFORMATION SCHEMA TABLES

User · Answer

Soln 1    mysql gt  select group concat  truncate      table name      from information schema tables where table schema  db name  into outfile   tmp a txt   mysql gt   tmp a txt    Soln 2    - Export only structure of a db - drop the database - import the  sql of structure    -- edit ----  earlier in solution 1  i had mentioned concat   instead of group concat   which would have not returned the desired result

User · Answer

here for i know here      SELECT Concat  TRUNCATE TABLE   table schema     TABLE NAME            FROM INFORMATION SCHEMA TABLES where  table schema in   databasename1   databasename2      If cannot delete or update a parent row  a foreign key constraint fails  That happens if there are tables with foreign keys references to the table you are trying to drop truncate   Before truncating tables All you need to do is   SET FOREIGN KEY CHECKS 0    Truncate your tables and change it  back to   SET FOREIGN KEY CHECKS 1     user this php code            truncate   mysql query  SELECT Concat  TRUNCATE TABLE   table schema     TABLE NAME       as tables query FROM INFORMATION SCHEMA TABLES where table schema in   databasename           while  truncateRow mysql fetch assoc  truncate             mysql query  truncateRow  tables query              gt    check detail here link

User · Answer

PHP single command  php -r   d  quot PUT YOUR DB NAME HERE quot    q  quot show tables quot    dt  quot drop table quot   exec  quot mysql -Nse   quot  q  quot   d quot    o   foreach  o as  e   mysql -e  quot  dt  e quot   d     Executed PHP script   d  quot PUT YOUR DB NAME HERE quot     q  quot show tables quot     dt  quot drop table quot     exec  quot mysql -Nse   quot  q  quot   d quot    o     foreach  o as  e     mysql -e  quot  dt  e quot   d

User · Answer

I found it most simple to just do something like the code below  just replace the table names with your own   important make sure the last line is always      SET FOREIGN KEY CHECKS 1   SET FOREIGN KEY CHECKS 0  TRUNCATE  table1   TRUNCATE  table2   TRUNCATE  table3   TRUNCATE  table4   TRUNCATE  table5   TRUNCATE  table6   TRUNCATE  table7   SET FOREIGN KEY CHECKS 1

User · Answer

Drop  i e  remove tables   mysql -Nse  show tables  DATABASE NAME   while read table  do mysql -e  drop table  table  DATABASE NAME  done   Truncate  i e  empty tables   mysql -Nse  show tables  DATABASE NAME   while read table  do mysql -e  truncate table  table  DATABASE NAME  done

User · Answer

I found this to drop all tables in a database   mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME   grep  DROP   mysql -uUSERNAME -pPASSWORD DATABASENAME   Usefull if you are limited by hosting solution  not able to drop a whole database    I modified it to truncate the tables  There is no  --add-truncate-table  for mysqldump  so i did   mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME   grep  DROP   sed -e  s DROP TABLE IF EXISTS TRUNCATE TABLE g    mysql -uUSERNAME -pPASSWORD DATABASENAME   works for me --edit  fixing a typo in the last command

User · Answer

This will print the command to truncate all tables   SELECT GROUP CONCAT Concat  TRUNCATE TABLE   table schema     TABLE NAME  SEPARATOR      FROM INFORMATION SCHEMA TABLES where table schema in   my db

User · Answer

truncate multiple database tables on Mysql  instance   SELECT Concat  TRUNCATE TABLE   table schema     TABLE NAME        FROM INFORMATION SCHEMA TABLES where  table schema in   db1 name   db2 name      Use Query  Result  to  truncate tables  Note  may   be you  will  get  this  error   ERROR 1217  23000   Cannot delete or update a parent row  a foreign key constraint fails   That happens if there are tables with foreign keys references to the table you are trying to drop truncate   Before truncating tables All you need to do is   SET FOREIGN KEY CHECKS 0    Truncate your tables and change it  back to   SET FOREIGN KEY CHECKS 1

User · Answer

To truncate a table  one must drop the foreign key constraints mapped to the columns in this table from other tables  in fact on all tables in the specific DB Schema    So  all foreign key constraints must be dropped initially followed by table truncation  Optionally  use the optimize table  in mysql  innodb engine esp  to reclaim the used data space size to OS after data truncation  Once data truncation is carried out  create the same foreign key constraints again on the same table   See below a script that would generate the script to carry out the above operations   SELECT CONCAT  ALTER TABLE   TABLE SCHEMA     TABLE NAME   DROP FOREIGN KEY   CONSTRAINT NAME      FROM INFORMATION SCHEMA TABLE CONSTRAINTS WHERE CONSTRAINT TYPE  FOREIGN KEY  AND TABLE SCHEMA   lt TABLE SCHEMA gt   UNION SELECT CONCAT  TRUNCATE TABLE   TABLE SCHEMA     TABLE NAME      FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA   lt TABLE SCHEMA gt   AND TABLE TYPE  BASE TABLE  UNION SELECT CONCAT  OPTIMIZE TABLE   TABLE SCHEMA     TABLE NAME      FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA   lt TABLE SCHEMA gt   AND TABLE TYPE  BASE TABLE  UNION SELECT CONCAT  ALTER TABLE   TABLE SCHEMA     TABLE NAME   ADD CONSTRAINT   CONSTRAINT NAME   FOREIGN KEY   COLUMN NAME       REFERENCES   REFERENCED TABLE NAME     REFERENCED COLUMN NAME       FROM INFORMATION SCHEMA KEY COLUMN USAGE WHERE CONSTRAINT NAME LIKE  FK   AND TABLE SCHEMA   lt TABLE SCHEMA gt   INTO OUTFILE  C  DB Truncate sql  LINES TERMINATED BY   n      Now  run the Db Truncate sql script generated  Benefits  1  Reclaim disk space 2  Not needed to drop and recreate the DB Schema with the same structure  Drawbacks  1  FK constraints should be names in the table with the name containing  FK  in the constraint name

User · Answer

SET FOREIGN KEY CHECKS   0   SELECT  str    CONCAT  TRUNCATE TABLE    table schema       table name       FROM   information schema tables WHERE  table type      BASE TABLE    AND  table schema IN   db1 name   db2 name     PREPARE stmt FROM  str   EXECUTE stmt   DEALLOCATE PREPARE stmt   SET FOREIGN KEY CHECKS   1

User · Answer

Here is a procedure that should truncate all tables in the local database   Let me know if it doesn t work and I ll delete this answer   Untested    CREATE PROCEDURE truncate all tables   BEGIN     -- Declare local variables    DECLARE done BOOLEAN DEFAULT 0     DECLARE cmd VARCHAR 2000       -- Declare the cursor    DECLARE cmds CURSOR    FOR    SELECT CONCAT  TRUNCATE TABLE    TABLE NAME  FROM INFORMATION SCHEMA TABLES      -- Declare continue handler    DECLARE CONTINUE HANDLER FOR SQLSTATE  02000  SET done 1      -- Open the cursor    OPEN cmds      -- Loop through all rows    REPEAT        -- Get order number       FETCH cmds INTO cmd         -- Execute the command       PREPARE stmt FROM cmd        EXECUTE stmt        DROP PREPARE stmt      -- End of loop    UNTIL done END REPEAT      -- Close the cursor    CLOSE cmds   END

User · Answer

TB    mysql -Bse  show tables from DATABASE     for i in   TB       do echo  Truncating table   i        mysql -e  set foreign key checks 0  set unique checks 0 truncate table DATABASE   i   set foreign key checks 1  set unique checks 1       sleep 1  done   --  David   Thank you for taking the time to format the code  but this is how it is supposed to be applied   -Kurt  On a UNIX or Linux box   Make sure you are in a bash shell  These commands are to be run  from the command line as follows   Note    I store my credentials in my    my cnf file  so I don t need to supply them on the command line   Note   cpm is the database name  I am only showing a small sample of the results  from each command   Find your foreign key constraints   klarsen Chaos    mysql -Bse  select concat table name    depends on    referenced table name               from information schema referential constraints              where constraint schema    cpm               order by referenced table name     approval external system depends on approval request address depends on customer customer identification depends on customer external id depends on customer credential depends on customer email address depends on customer approval request depends on customer customer status depends on customer customer image depends on customer   List the tables and row counts   klarsen Chaos    mysql -Bse  SELECT table name  table rows FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA    cpm     cat -n   1  address 297  2  approval external system    0  3  approval request    0  4  country 189  5  credential  468  6  customer    6776  7  customer identification 5631  8  customer image  2  9  customer status 13639   Truncate your tables   klarsen Chaos    TB    mysql -Bse  show tables from cpm     for i in   TB   do echo  Truncating table   i    mysql -e  set foreign key checks 0  set unique checks 0 truncate table cpm   i   set foreign key checks 1  set unique checks 1   sleep 1  done    Truncating table address Truncating table approval external system Truncating table approval request Truncating table country Truncating table credential Truncating table customer Truncating table customer identification Truncating table customer image Truncating table customer status   Verify that it worked   klarsen Chaos    mysql -Bse  SELECT table name  table rows FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA    cpm     cat -n   1  address 0  2  approval external system    0  3  approval request    0  4  country 0  5  credential  0  6  customer    0  7  customer identification 0  8  customer image  0  9  customer status 0 10  email address   0   On a Windows box   NOTE   cpm is the database name  C   gt for  F  tokens     a IN   mysql -Bse  show tables  cpm   do mysql -e  set foreign key checks 0  set unique checks 0  truncate table  a  foreign key checks 1  set unique checks 1  cpm

User · Answer

Use this and form the query  SELECT Concat  TRUNCATE TABLE   table schema     TABLE NAME        FROM INFORMATION SCHEMA TABLES where  table schema in  db1 db2  INTO OUTFILE   path to file sql     Now use this to use this query  mysql -u username -p  lt  path to file sql   if you get an error like this  ERROR 1701  42000  at line 3  Cannot truncate a table referenced in a foreign key constraint   the easiest way to go through is at the top of your file add this line   SET FOREIGN KEY CHECKS 0    which says that we don t want to check the foreign key constraints while going through this file   It will truncate all tables in databases db1 and bd2

User · Answer

if using sql server 2005  there is a hidden stored procedure that allows you to execute a command or a set of commands against all tables inside a database  Here is how you would call TRUNCATE TABLE with this stored procedure   EXEC  sp MSforeachtable   command1  TRUNCATE TABLE      Here is a good article that elaborates further   For MySql  however  you could use mysqldump and specify the --add-drop-tables and --no-data options to drop and create all tables ignoring the data  like this   mysqldump -u USERNAME  -p PASSWORD  --add-drop-table --no-data  DATABASE    mysqldump usage guide from dev mysql

User · Answer

I am not sure but I think there is one command using which you can copy the schema of database into new database  once you have done this you can delete the old database and after this you can again copy the database schema to the old name

[mysql] Truncate all tables in a MySQL database in one command?

Examples related to mysql