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.
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 ;Comment by jess — February 13, 2007 @ 9:54 pm
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 ;
Comment by diyism — September 23, 2008 @ 12:07 am
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