A Little Noise

10Nov/063

Drop all tables

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.

Filed under: MySQL FAQ Leave a comment
Comments (3) Trackbacks (0)
  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))
    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. 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. Regarding to comments: still too complicated.
    Just use the first command with addition:
    and table_name like "something_%";


Leave a comment

No trackbacks yet.