Truncate all tables in a MySQL database in one com

Question

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.

Answer 1

I am not sure but I think there is one command using which you can copy the schema of database into new database, once you have done this you can delete the old database and after this you can again copy the database schema to the old name.

Answer 2

SET FOREIGN_KEY_CHECKS = 0;

SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('db1_name','db2_name');

PREPARE stmt FROM @str;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

Answer 3

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!

Answer 4

here for i know here

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

If 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; 

user this php code

    $truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename')");

    while($truncateRow=mysql_fetch_assoc($truncate)){

        mysql_query($truncateRow['tables_query']);

    }
?>

check detail here link

Answer 5

An idea could be to just drop and recreate the tables?

EDIT:

@Jonathan Leffler: True

Other Suggestion (or case you dont need to truncate ALL tables):

Why not just create a basic stored procedure to truncate specific tables

CREATE PROCEDURE [dbo].[proc_TruncateTables]
AS
TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
GO

Answer 6

I found it most simple to just do something like the code below, just replace the table names with your own. important make sure the last line is always SET FOREIGN_KEY_CHECKS=1;

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;