Alex Rivera | Logout

How can I delete the contents of all tables in my database in phpMyAdmin without dropping the database?

Asked 2010-03-27T15:09:53.837
16

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.

Edit
Report

1 Answer

1
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 #
answered 2010-03-27T15:45:10.267

Your Answer