MySQL: How to TRUNCATE all Tables of a Database?

In this post, I am sharing one script to generate TRUNCATE TABLE script for all tables of MySQL Database Server.

Generally, Database Developer creates a testing tables in development or report database server and even sometimes it is required to TRUNCATE all tables of a database.

I have prepared one small script using INFORMATION_SCHEMA.TABLES which generates TRUNCATE TABLE script for all tables and you can also apply filters like: database_name, table_name.

1 2 3

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

You can copy the result of this script and can perform TRUNCATE operation for multiple tables.

During TRUNCATE operation if you get any error like foreign key constraint fail, then you can enable and disable FOREIGN_KEY_CHECKS.

Error like:

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

Disable Foreign Key:

1	SET FOREIGN_KEY_CHECKS=0;

Enable Foreign Key:

1	SET FOREIGN_KEY_CHECKS=1;