Truncate all tables in a MySQL database in one command?

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

---

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

---

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

---

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']);

    }
?>

check detail here link

---

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

---

This worked for me. Change database, username and password accordingly.

mysql -Nse 'show tables' -D DATABASE -uUSER -pPWD | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0;drop table \`$table\`;SET FOREIGN_KEY_CHECKS = 1;"; done | mysql DATABASE -uUSER -pPWD

---

Ans by battousaix is perfect! I just used his answer and created the final working command for truncate database tables.

mysql -P 3306 -h YOUR_HOST_HERE -u YOUR_USERNAME_HERE -pYOUR_PASSWORD_HERE -Nse 'show tables' DATABASE_NAME | while read table; do mysql -P 3306 -h YOUR_HOST_HERE -u YOUR_USERNAME_HERE -pYOUR_PASSWORD_HERE -e "SET FOREIGN_KEY_CHECKS = 0; truncate table $table" DATABASE_NAME; done

The above command will work perfectly for the MySQL server.

Also, It's included.

SET FOREIGN_KEY_CHECKS = 0

---

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;

---

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

---

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.

---

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.

---

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:\>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

---

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

---

mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql

---

Soln 1)

mysql> select group_concat('truncate',' ',table_name,';') from information_schema.tables where table_schema="db_name" into outfile '/tmp/a.txt';
mysql> /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

---

We can write a bash script like below

truncate_tables_in_mysql() {
    type mysql >/dev/null 2>&1 && echo "MySQL present." || sudo apt-get install -y mysql-client
    
    tables=$(mysql -h 127.0.0.1 -P $MYSQL_PORT -u $MYSQL_USER  -p$MYSQL_PASSWORD -e "USE $BACKEND_DATABASE;    
SHOW TABLES;")
    tables_list=($tables)
    
    query_string="USE $BACKEND_DATABASE; SET FOREIGN_KEY_CHECKS = 0;"
    for table in "${tables_list[@]:1}"
    do
        query_string="$query_string TRUNCATE TABLE \`$table\`; "
    done
    query_string="$query_string SET FOREIGN_KEY_CHECKS = 1;"
    
    mysql -h 127.0.0.1 -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "$query_string"
}

You can replace env variables with your MySQL details. Using one command you can truncate all the tables in a DB.

---

PHP single command:

php -r '$d="PUT_YOUR_DB_NAME_HERE"; $q="show tables"; $dt="drop table"; exec("mysql -Nse \"$q\" $d", $o); foreach($o as $e) `mysql -e "$dt $e" $d`;'

Executed PHP script:

$d="PUT_YOUR_DB_NAME_HERE"; 
$q="show tables"; 
$dt="drop table"; 

exec("mysql -Nse \"$q\" $d", $o); 

foreach($o as $e)
  `mysql -e "$dt $e" $d`;

---

I find the top answer to be amazing. However it fails when you have an authenticated MySQL Database user.

Here is a solution built on top of the top answer I linked. This solution securely handles authentication without having to:

Type the password for each table

Worry about your password leaking somewhere

Side-effects on other MySQL cnf files (~/.my.cnf) For more details on what these files do, check out resources section at the bottom of this answer.

1. Create local .my.cnf file

vi .temp.my.cnf

[client]
user=<admin_user_goes_here>
password=<admin_password_goes_here>

2. Truncate or drop all tables

2.A Truncate All Tables (empty only)

mysql --defaults-extra-file=.temp.my.cnf -Nse 'show tables' <db_name_goes_here> | while read table; do mysql --defaults-extra-file=.temp.my.cnf -e "truncate table $table" <db_name_goes_here>; done

2.B Drop All Tables (remove entirely)

mysql --defaults-extra-file=.temp.my.cnf -Nse 'show tables' <db_name_goes_here> | while read table; do mysql --defaults-extra-file=.temp.my.cnf -e "drop table $table" <db_name_goes_here>; done

3. Cleanup

Delete your user-password file

rm -rf .temp.my.cnf

Resources:

https://rtcamp.com/tutorials/mysql/mycnf-preference/

https://dev.mysql.com/doc/refman/8.0/en/option-files.html

https://dev.mysql.com/doc/refman/8.0/en/option-file-options.html#option_general_defaults-extra-file

---

Small addition to @Mathias Bynens's answer. When I run this I got an error because foreign key check

mysql -Nse 'SHOW TABLES' <database_name> | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; DROP TABLE $table" <database_name>; done

If there are views in the database it returns an error. I had to clear views manually by drop view <view_name>;

---

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

---

<?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->error;
}

$conn->close();

?>

Here is code snippet which I use to clear a table. Just change $conn info and TABLE_NAME.

---