Drop all tables

Comments

3 responses to “Drop all tables”

1. 

   February 13, 2007

   jess

   here is a stored procedure to do it too:

   -- drop tables that match a given pattern
   drop procedure if exists drop_tables_like;
   delimiter $
   create procedure drop_tables_like(tblpattern varchar(100))
   begin
   	declare tblcount int default 0;
   	declare tblname varchar(100);
   	declare done int default 0;
   	declare curs cursor for select table_name from
   		information_schema.tables where table_name like tblpattern
   					  and table_schema = 'test';
   	declare continue handler for sqlstate '02000' set done = 1;
   	open curs;
   	myloop: loop
   	      fetch curs into tblname;
   	      if done then close curs; leave myloop; end if;
   	      set @dropstmt = concat("drop table `", tblname, "`");
   	      prepare stmt from @dropstmt;
   	      execute stmt;
   	      deallocate prepare stmt;
   	      set tblcount = tblcount + 1;
   	end loop;
   	select tblcount as "Tables Deleted";
   end $
   delimiter ;
2. 

   September 23, 2008

   diyism

   Guy, thus may be more concise:

   delimiter $$
create procedure drop_tables_like(pattern varchar(255), db varchar(255))
begin
  select @str_sql:=concat('drop table ', group_concat(table_name))
  from information_schema.table
  where table_schema=db and table_name like pattern;

     prepare stmt from @str_sql;
     execute stmt;
     drop prepare stmt;
   end$$

   call drop_tables_like('kw_%', 'db_1')$$

   drop procedure if exists drop_tables_like$$
delimiter ;
3. 

   September 21, 2009

   Slava

   Regarding to comments: still too complicated.
   Just use the first command with addition:
   … and table_name like "something_%";

This site uses Akismet to reduce spam. Learn how your comment data is processed.