TRUNCATE data from all tables in a Database

Question

For some work, the requirement is such that we want to retain the table and database structure while truncating all data in the multiple table at one go. Since Truncate Table_name only truncates one table at time. Is there a way to truncate multiple table? Help would be appreciated.

Answer 1

The easiest way may be the following:

If you have foreign key constraint then temporarily set it OFF.

SET FOREIGN_KEY_CHECKS=0;

To set it ON again:

SET FOREIGN_KEY_CHECKS=1;

---

To truncate all tables under a particular database

SELECT 
CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') AS truncateCommand
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME_HERE';

To truncate all tables out of all databases

SELECT 
    CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') AS truncateCommand
    FROM information_schema.TABLES;

---

And you will get output like that:

TRUNCATE TABLE your_table_1;
TRUNCATE TABLE your_table_2;
TRUNCATE TABLE your_table_3;
TRUNCATE TABLE your_table_4;
TRUNCATE TABLE your_table_5;
TRUNCATE TABLE your_table_6;
TRUNCATE TABLE your_table_7;
TRUNCATE TABLE your_table_8;
.
.
etc..

Now grab these truncate commands and execute all.

_{You can approach this way to avoid the hassle of writing a stored procedure to get it done if and only if it's a one time job}