truncate all table in mysql database

Question

Is it possible to truncate all table in mysql database ? what is query for that .

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) — Robbie Wxyz, Mar 09 '15 at 00:01

score 10 · Answer 1 · answered Jan 30 '14 at 17:40

10

Continuing from @Pablo pipes weren't concatenating for me - and I wanted to restrict it to a single database and then only tables

SELECT CONCAT('truncate table ',table_name,';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<<YOUR-DB-NAME>>'
AND TABLE_TYPE = 'BASE TABLE';

answered Jan 30 '14 at 17:40

cloakedninjas

4,007
2
31
45

I had same issue that pipes did not concatenate (MySQL 5.6.15). This worked for me. – nagu Jul 23 '16 at 09:46

Pablo Santa Cruz · Accepted Answer · 2010-07-01T12:42:38.420

6

You can do this query:

select 'truncate table ' || table_name || ';' 
  from INFORMATION_SCHEMA.TABLES;

Then save results to a script and run it.

Other possibility might be,

Run the query
Copy the results to the clipboard (COPY)
Paste the results into the MySQL command interpreter (PASTE)

Done.

If you just run the query you will understand what I am trying to say.

edited Jul 01 '10 at 12:42

answered Jul 01 '10 at 12:27

Pablo Santa Cruz

176,835
32
241
292

sorry but, I can't understand your statement , save results to a script and run it. – chetan Jul 01 '10 at 12:33

score 6 · Answer 3 · edited Mar 16 '17 at 19:49

6

You can simply select all tables and then select truncate all.

edited Mar 16 '17 at 19:49

Drew Szurko

1,601
1
16
32

answered Mar 16 '17 at 18:22

Cristiana Pereira

61
1
2
3

This is really helpful. Thank you very much. I am looking for this type of solution and it is application in mysqlworkbench as well. – kailash gaur Jul 12 '17 at 07:36

score 3 · Answer 4 · answered Jul 01 '10 at 12:24

3

Not possible using a single sql query.

answered Jul 01 '10 at 12:24

Dogbert

212,659
41
396
397

2

Which is probably a Good Thing. – Brian Hooper Jul 01 '10 at 12:57

score 3 · Answer 5 · edited Oct 01 '16 at 07:29

Here I leave you a stored procedure to reset your database to cero

CREATE DEFINER=`root`@`localhost` PROCEDURE `reset_database`(DB_NAME VARCHAR(100))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(100);
DECLARE cur CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = DB_NAME AND table_type = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

SET FOREIGN_KEY_CHECKS = 0;
read_loop: LOOP
FETCH cur INTO tableName;
  IF done THEN LEAVE read_loop; END IF;
  SET @s = CONCAT('truncate table ',tableName);
  PREPARE stmt1 FROM @s;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;
END LOOP;
SET FOREIGN_KEY_CHECKS = 1;

CLOSE cur;
END;

This does not work on my machine. "You have an error in your SQL syntax" — Fernando César, Dec 31 '18 at 00:31

score 0 · Answer 6 · answered Mar 28 '18 at 11:20

There might be a situation that the truncation fails due to foreign key constraints. If you would still like to force truncation then you can select all tables for truncation as suggested by Cristiana Pereira.

Then, click "Review SQL" instead of clicking on "Truncate" in the following window. Follow these steps

Copy all queries from the dialog box and then close it.
click on "Create a new SQL tab for executing queries".
Paste all queries between these 2 lines and execute.

set foreign_key_checks = 0;
set foreign_key_checks = 1;

score 0 · Answer 7 · answered Feb 21 '23 at 09:56

In phpMyAdmin go to the "Structure" panel. You will see the list of the tables of you database. Below the table check the option "Check all" to select all tables. Then in the combobox "With selected:" choose "Empty". PhpMyAdmin will ask you "Do you really want to execute the following query?". Before answering yes uncheck the option "Enable foreign key checks"

truncate all table in mysql database

7 Answers7