How to Truncate a table in MySQL | How to Truncate all Tables in MySQL - MySQL Developer Tutorial

How to Truncate table in MySQL | How to Truncate all Tables in MySQL 

To truncate single table in MySQL or MairaDB you can use below syntax.

Truncate table TableName;

Truncate All Tables in MySQL or MariaDB :

To truncate all tables in a database, you can generate the Truncate script for all the tables in a database. One of the other thing to remember, as some of the Tables might have Primary Key - Foreign Key relationship. First of all disable Foreign Key constraints and then generate Truncate table script and then at the enable the Foreign key constraints again.

1) Disable Foreign Key Constraint in database: 

SET FOREIGN_KEY_CHECKS=0;

2) Generate Truncate table script :

Use below to generate Truncate table statements, copy the output and run in new window.

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME,';') as Query
FROM information_schema.TABLES

WHERE TABLE_SCHEMA = "YourDatabaseName"

3) Enable Foreign Key Constraints :

Enable the Foreign Key Constraint in your database after truncating all the tables by using in step 2.

SET FOREIGN_KEY_CHECKS=1;