Drop Multiple table

Question

How to drop multiple Table in MySQL Database. (Table have same suffix) Please Give me some idea ?

I am trying to drops multiple table having same Suffix 00000

Query I am aplying is

Drop table Like '%00000';

You can use `show tables from db like '%00000'` And then copy paste into a text editor and build a comma separated list to feed `drop table `. — Tamil Selvan C, Jul 01 '15 at 02:59

score 0 · Answer 1 · answered Jul 01 '15 at 02:57

You can create one procedure

drop procedure if exists droplike;
delimiter //
create procedure droplike(pattern varchar(20))
begin
  set group_concat_max_len = 65535;
  select @drop:= concat( 'drop table ', group_concat(table_name) , ';' ) from information_schema.tables where table_schema = "database_name" and table_name like pattern;
  prepare statement from @drop;
  execute statement;
end //
delimiter ;

Then, call your procedure like below

call droplike("0000%");

Drop Multiple table

1 Answers1