How to TRUNCATE multiple Tables in MySQL

In this post we will learn about how can we truncate multiple tables in MySQL database management system.
truncate multiple tables in MySQL

Why we cannot TRUNCATE Multiple Tables in MySQL directly:

The basic syntax of TRUNCATE is:
In this case we can truncate only one table at a time from a single database.
What if we need to TRUNCATE multiple tables from different databases in MySQL which could include more than 1000 tables? Do you want write this line by line? I guess no, so let’s find out how to do this:

Solution:

This can be achieved using metadata information of  database. INFORMATION_SCHEMA database holds this information, it contains all the information like information about all the databases that the MySQL server maintains, information of all the users that can access the MySQL server etc.
Use the following query to get the list of tables from multiple databases:
Now execute the result of this query to truncate all the required tables.


We can also execute this query in a way so that we do not need to copy paste the output of it, simple take the ouput in a file and execute it.
Now we have output of this query in output.sql, now execute this:
Note: We may get this error:
That happen if there are tables with foreign keys references to the table you are trying to drop.


For this you can read this post to know the details:
For the quick answer:
Before truncating tables All you need to check  FOREIGN_KEY_CHECKS variable value
Truncate your tables and change it back to
Hope this helps, comment for any suggestion, concerns.

at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Pages

Created By SoraTemplates | Distributed By Free Blogger Templates