CODEWITHSUNDEEP
mysql
devang
devang

Reputation: 4527

Truncate all tables in a MySQL database in one command?

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.

Upvotes: 422

Views: 595731

Answers (30)

Nemo
Nemo

Reputation: 3164

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 at least version 4.5.0 or higher to get this checkbox.

It's not MySQL CLI-fu, but hey, it works!

Upvotes: 86

Subfire
Subfire

Reputation: 87

As it happens, I recently needed to clear all tables in the database with the specified name prefix.

(export MYSQL_PWD=the_password; dbNameMatch="the_database_name_prefix%"; databaseList=$(mysql -u root -Nse "SHOW DATABASES LIKE '$dbNameMatch'"); for dbName in $databaseList; do mysql -uroot -Nse 'show tables' "$dbName" | while read table; do mysql -uroot -e "truncate table \`$table\`" "$dbName" & done; done)

This single-line command works wor me. holp it helpful for you.

Upvotes: 0

lalit choudhary
lalit choudhary

Reputation: 2425

The following query will generate a list of individual truncate commands for all database tables in a Mysql schema(s). (Replace dbSchemaName1 with name of your Db schema.)

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
    FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN ('dbSchemaName1','dbSchemaName2');

Copy the query results (which might look like the following) and paste the list of truncate commands into a SQL query tab in MySQL Worbench or your query command tool of choice:

TRUNCATE TABLE dbSchemaName1.table1;
TRUNCATE TABLE dbSchemaName1.table2;
TRUNCATE TABLE dbSchemaName1.table3;

Note: you may receive the following error:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This occurs if there are tables with foreign keys references to the table you are trying to drop/truncate.

To resolve this turn off foreign key checks before running the truncate commands:

SET FOREIGN_KEY_CHECKS=0;  -- turn off foreign key checks
TRUNCATE TABLE dbSchemaName1.table1;  -- truncate tables
TRUNCATE TABLE dbSchemaName1.table2;
TRUNCATE TABLE dbSchemaName1.table3;
SET FOREIGN_KEY_CHECKS=1;  -- turn on foreign key checks

Upvotes: 228

Lakindu Akash
Lakindu Akash

Reputation: 1036

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

Upvotes: 0

om-ha
om-ha

Reputation: 3602

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:

Upvotes: 1

Chandresh M
Chandresh M

Reputation: 3828

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

Upvotes: 2

Grzegorz Adam Kowalski
Grzegorz Adam Kowalski

Reputation: 5585

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

Upvotes: 1

Manisha Bayya
Manisha Bayya

Reputation: 169

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.

Upvotes: 0

Akif
Akif

Reputation: 484

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

Upvotes: 2

George Moik
George Moik

Reputation: 465

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;

Upvotes: 24

user3357946
user3357946

Reputation: 327

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;

Upvotes: 21

Jydipsinh Parmar
Jydipsinh Parmar

Reputation: 502

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

Upvotes: 3

battousaix
battousaix

Reputation: 3923

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

Upvotes: 389

bebbo
bebbo

Reputation: 2959

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

Upvotes: 6

Gaurav Gupta
Gaurav Gupta

Reputation: 5416

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

Upvotes: 16

James Leveille
James Leveille

Reputation: 21

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

Upvotes: -4

Angelin Nadar
Angelin Nadar

Reputation: 9300

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

Upvotes: 0

emanuele
emanuele

Reputation: 2589

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

Upvotes: 0

Roel van Duijnhoven
Roel van Duijnhoven

Reputation: 850

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

Upvotes: 0

Kurt Larsen
Kurt Larsen

Reputation: 1

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"
  1. approval_external_system depends on approval_request
  2. address depends on customer
  3. customer_identification depends on customer
  4. external_id depends on customer
  5. credential depends on customer
  6. email_address depends on customer
  7. approval_request depends on customer
  8. customer_status depends on customer
  9. 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
  1. Truncating table address
  2. Truncating table approval_external_system
  3. Truncating table approval_request
  4. Truncating table country
  5. Truncating table credential
  6. Truncating table customer
  7. Truncating table customer_identification
  8. Truncating table customer_image
  9. 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

Upvotes: 0

Vinod Kumar
Vinod Kumar

Reputation: 489

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.

Upvotes: 3

Pavel Mocan
Pavel Mocan

Reputation: 21

I know this isn't exactly one command, but the desired result can be achieved from within phpMyAdmin by following these steps:

  1. Select (all) tables to be removed (Check All)
  2. Select "Drop" / "Truncate" from the "With selected:" list
  3. On the confirmation page ("Do you really want to:") copy the query (everything with the red background)
  4. Go at the top and click on SQL and write: "SET FOREIGN_KEY_CHECKS=0;" then paste the previously copied query
  5. 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.

Upvotes: 1

Anand
Anand

Reputation: 61

  1. 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).
  2. So, all foreign key constraints must be dropped initially followed by table truncation.
  3. Optionally, use the optimize table (in mysql, innodb engine esp) to reclaim the used data space/size to OS after data truncation.

  4. 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='<TABLE SCHEMA>'
UNION
SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
UNION
SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='<TABLE SCHEMA>' 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='<TABLE SCHEMA>'
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.

Upvotes: 6

cedd
cedd

Reputation: 1861

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

Upvotes: 1

mbjungle
mbjungle

Reputation: 239

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

Upvotes: 23

beach
beach

Reputation: 8640

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

Upvotes: 25

Bj&#248;rn T.
Bj&#248;rn T.

Reputation: 29

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

Upvotes: 2

Dostee
Dostee

Reputation: 744

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

Upvotes: 4

Mark Redman
Mark Redman

Reputation: 24535

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

Upvotes: -1

beach
beach

Reputation: 8640

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;

Upvotes: 1

Related Questions