CODEWITHSUNDEEP
mysql
Leem.fin
Leem.fin

Reputation: 42672

mySQL: command to truncate all tables

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.

Upvotes: 1

Views: 5680

Answers (2)

brave
brave

Reputation: 11

My favorite way is to use Navicat.

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

Upvotes: 1

Marco
Marco

Reputation: 57593

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 ;

Upvotes: 2

Related Questions