How Can I Delete The Contents Of All Tables In My Database In phpMyAdmin Without Dropping The Database?

Question

How Can I Empty The Contents Of All Tables In My Database In phpMyAdmin Without Dropping Any Of The Tables In The Database?

Since I Do This Several Times An Hour While In Development, I'd Rather Not Individually Click "Empty" On All 60+ Tables Every Time.

Thanks In Advance.

Answer 1

Unfortunately, there is no TRUNCATE DATABASE or equivalent. With that said, you could probably use some kind of stored procedure that go through all tables in your database and truncate them. I found something of that kind here, but I don't know whether it works. (You should probably read the comment discussion too)

Answer 2

Create a SQL script with multiple DELETE statements (one for each table) and execute it.

Get into phpMyAdmin and select the database that you want. Select the SQL tab and paste the SQL script into the window. Hit Go.

Look here too:

Drop all tables from a MySQL Database without deletion

Answer 3

you could start with this query

SELECT T.*
FROM INFORMATION_SCHEMA.tables T
WHERE T.table_type = 'BASE TABLE'

And iterate through those results to build a dynamic SQL string to the tune of 'DELETE FROM ' + T.Table_Name

Answer 4

drop procedure if exists truncate_tables;

delimiter #
create procedure truncate_tables()
begin
 declare tab_name varchar(64);
 declare done tinyint unsigned default 0;

 declare table_cur cursor for select t.table_name
 from 
  information_schema.schemata s
  inner join information_schema.tables t on s.schema_name = t.table_schema
 where
   s.schema_name = database() and t.table_type = 'BASE TABLE';

 declare continue handler for not found set done = 1;

 open table_cur;
 repeat
   fetch table_cur into tab_name;
   set @cmd = concat('truncate table ', tab_name);

   prepare stmt from @cmd;
   execute stmt;
 until done end repeat;

 close table_cur;
end #

Answer 5

It is incredibly trivial to script this, I'm sure you can do a command-line script in whatever language your application code uses. It may take as much as 5 minutes to get it working.