Truncate all tables in a MySQL database in one command?

user_232701 0
Viewed 515647

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.

mysql
30 Answers

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
beach_538920 
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_21545470

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_1186900

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
user_21903220

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

user_5053550

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

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.

user_2131330

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.

user_72759930

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

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:

user_108300910

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

user_87087100

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_459670
Related