Truncate all tables in a MySQL database in one command?

Drop (i.e. remove tables)

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

truncate multiple database tables on Mysql instance

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/truncate.

Before truncating tables All you need to do is:

SET FOREIGN_KEY_CHECKS=0;

Truncate your tables and change it back to

SET FOREIGN_KEY_CHECKS=1;

Use phpMyAdmin in this way:

Database View => Check All (tables) => Empty

If you want to ignore foreign key checks, you can uncheck the box that says:

[ ] Enable foreign key checks

You'll need to be running atleast version 4.5.0 or higher to get this checkbox.

Its not MySQL CLI-fu, but hey, it works!

MS SQL Server 2005+ (Remove PRINT for actual execution...)

EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''

If your database platform supports INFORMATION_SCHEMA views, take the results of the following query and execute them.

SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

Try this for MySQL:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES

Adding a semicolon to the Concat makes it easier to use e.g. from within mysql workbench.

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES