Truncate multiple database tables In MySQL

To  truncate table we use TRUNCATE command As follows.

TRUNCATE  TABLE  <table_name>;

What if  you  want to  TRUNCATE  ALL tables from different   databases in mysql ,It is possible using  metadata information of  database. INFORMATION_SCHEMA database created while installing  mysql ,which  contains  all information like stores information about all the other databases that the MySQL server maintains.

Here  Is the Query  which  gives output  script with  truncate commands   for all table.You  don’t  have to  write truncate command  one by  one  for each  table.

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1_name,db2_name);

Use Query Result to truncate tables

Note: may be you will get this error:

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

That happens if there are tables with foreign keys references to the table you are trying to drop.

Before truncating tables All you need to check  FOREIGN_KEY_CHECKS variable value

SET FOREIGN_KEY_CHECKS=0;   (This will  disable  FOREIGN KEY check)

Truncate your tables and change it back to

SET FOREIGN_KEY_CHECKS=1;
 
Advertisements