mySQL: command to truncate all tables

Question

I am using MySQL.

What is the mysql command to truncate all tables of my database?

I mean what is the mysql command which empty all tables in my DB not drop all tables.

possible duplicate of [Truncate all tables in a MySQL database in one command?](http://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command) — Amir Raminfar, Nov 01 '11 at 15:57

score 2 · Answer 1 · answered Nov 01 '11 at 15:57

Try this code found here

DELIMITER $$
CREATE PROCEDURE TruncateTables()
BEGIN
DECLARE done BOOL DEFAULT FALSE;
DECLARE truncate_command VARCHAR(512);
DECLARE truncate_cur
 CURSOR FOR /*This is the query which selects the tables we want to truncate*/
  SELECT CONCAT('TRUNCATE TABLE ',table_name)
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'prefix_%';
DECLARE
  CONTINUE HANDLER FOR
  SQLSTATE '02000'
   SET done = TRUE;

OPEN truncate_cur;

truncate_loop: LOOP
 FETCH truncate_cur INTO truncate_command;
 SET @truncate_command = truncate_command;

 IF done THEN
  CLOSE truncate_cur;
  LEAVE truncate_loop;
 END IF;

 /*Main part - preparing and executing the statement*/
 PREPARE truncate_command_stmt FROM @truncate_command;
 EXECUTE truncate_command_stmt;

END LOOP;
END$$

DELIMITER ;

score 1 · Answer 2 · edited Dec 23 '13 at 07:10

1

My favorite way is to use Navicat.

Use ControlA to check all tables. Then right click and choose "truncate table".

edited Dec 23 '13 at 07:10

Michael Petrotta

59,888
27
145
179

answered Dec 23 '13 at 06:46

brave

11
1

mySQL: command to truncate all tables

2 Answers2