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.

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:

Disable Foreign Key:

Enable Foreign Key:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of