Truncate all tables in a MySQL database in one com

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.

Answer 1

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;

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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