CODEWITHSUNDEEP
mysql
chetan
chetan

Reputation: 3255

truncate all table in mysql database

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

Upvotes: 13

Views: 24559

Answers (7)

nix86
nix86

Reputation: 3037

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"

Upvotes: 0

codejunkie
codejunkie

Reputation: 968

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

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

set foreign_key_checks = 0;
set foreign_key_checks = 1;

Upvotes: 0

Cristiana Pereira
Cristiana Pereira

Reputation: 61

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

enter image description here

Upvotes: 6

teteArg
teteArg

Reputation: 4014

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;

Upvotes: 3

cloakedninjas
cloakedninjas

Reputation: 4176

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

Upvotes: 10

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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,

  1. Run the query
  2. Copy the results to the clipboard (COPY)
  3. 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.

Upvotes: 6

Dogbert
Dogbert

Reputation: 222158

Not possible using a single sql query.

Upvotes: 3

Related Questions