Is there a query (command) to truncate all the tables in a database in one operation? I want to know if I can do this with one single query.
Connect to the target database. Select all tables from the left sidebar. Right-click and choose delete, or simply hit delete button. Press Cmd + S to commit changes to the server.
No, you can only truncate a single table with TRUNCATE command.
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done
The following query will generate a list of individual truncate commands for all database tables in a Mysql schema(s). (Replace dbSchemaName1
with name of your Db schema.)
SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN ('dbSchemaName1','dbSchemaName2');
Copy the query results (which might look like the following) and paste the list of truncate commands into a SQL query tab in MySQL Worbench or your query command tool of choice:
TRUNCATE TABLE dbSchemaName1.table1;
TRUNCATE TABLE dbSchemaName1.table2;
TRUNCATE TABLE dbSchemaName1.table3;
Note: you may receive the following error:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
This occurs if there are tables with foreign keys references to the table you are trying to drop/truncate.
To resolve this turn off foreign key checks before running the truncate commands:
SET FOREIGN_KEY_CHECKS=0; -- turn off foreign key checks
TRUNCATE TABLE dbSchemaName1.table1; -- truncate tables
TRUNCATE TABLE dbSchemaName1.table2;
TRUNCATE TABLE dbSchemaName1.table3;
SET FOREIGN_KEY_CHECKS=1; -- turn on foreign key checks
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With