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.
SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
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 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Disable Foreign Key:
Enable Foreign Key:
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.