A Little Noise

November 10, 2006

Drop all tables

Filed under: MySQL FAQ — snoyes @ 10:06 pm

Since there exists neither DROP TABLE * nor DROP TABLE WHERE name LIKE ‘something’, here are two ways to achieve the same:

For version 5.0+:

mysql -N -e "SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') FROM information_schema.tables WHERE table_schema = 'dbName'" | mysql dbName

For any version on a platform with grep available:

mysqldump --add-drop-table -d dbName | grep -e "DROP TABLE" | mysql dbName

For any version on Windows:

mysqldump --add-drop-table -d dbName | findstr /B "DROP TABLE" | mysql dbName

You can alter the WHERE clause of the first or the grep of the second to support whatever specifics you like, such as all the tables that have a common prefix.


  1. 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))
    	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 ;

    Comment by jess — February 13, 2007 @ 9:54 pm

  2. Guy, thus may be more concise:

    delimiter $$
    create procedure drop_tables_like(pattern varchar(255), db varchar(255))
      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;

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

    drop procedure if exists drop_tables_like$$
    delimiter ;

    Comment by diyism — September 23, 2008 @ 12:07 am

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

    Comment by Slava — September 21, 2009 @ 11:52 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress