Mysql dropping multiple tables.
Hey there..
recently i was wondering how could i just use drop to drop multiple tables with a wildcard..??
googled it found out that u cant do drop tables with wildcard so now what first what i did was
to look for tables with wild card
show tables like ‘phpbb_%’;
Got a reply with all the tables of phpBB
then
DROP TABLE phpbb_auth_access,
phpbb_banlist,
phpbb_categories,
phpbb_config,
phpbb_confirm,
phpbb_disallow,
phpbb_forum_prune,
phpbb_forums,
phpbb_groups,
phpbb_posts,
phpbb_posts_text,
phpbb_privmsgs,
phpbb_privmsgs_text,
phpbb_ranks,
phpbb_search_results,
phpbb_search_wordlist,
phpbb_search_wordmatch,
phpbb_sessions,
phpbb_sessions_keys,
phpbb_smilies,
phpbb_themes,
phpbb_themes_name,
phpbb_topics,
phpbb_topics_watch,
phpbb_user_group,
phpbb_users,
phpbb_vote_desc,
phpbb_vote_results,
phpbb_vote_voters,
phpbb_words;
Sh00t all done 
Now if you are lucky enough to have a ssh access on the server then you can do some tweaking with the mysql.
Let see how its done!
type this on your bash
$ mysql -u webmaster -p -e “show tables like ’stup_%’” web > result.txt
Replace webmaster with the database user and web with the database name
you will have a file named result.txt
now
gawk -F \n ‘{print “”$1″,” }’ result.txt > alltables.sql
you will then have a file name alltables.sql
next open up alltables.sql and replace first line with
DROP TABLE
and the last line should end with ;
then on bash
mysql -u webmaster -p web < alltables.sql
and all the tables are gone 
Regards
Azeem.
Excellent thinking.
Or you could try this:
mysql -s -u webmaster -p web -e “show tables like ’stup_%’” |xargs mysqldump -u webmaster -p web
I mean, you could use another mysql command or mysqldump or whatever in the second command.
Yeah there are multiple way i have seen another workaround on php as well.. about this thing!
It’d be really easy to scribble up a Perl script to do this. But, really, the best way is to keep it on the command line. No sense having extra scripts all over the place.
True.
if all you have is phphmyadmin, do a select of the table listings in the left iframe. paste into a text editor, and replase “# Browse” with DROP TABLE
Firefox…other browsers YMMV
Guys, 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.tables
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 ;
You can also try this:
http://www.gottolearn.com/2009/08/26/dropping-multiple-tables-using-wildcard-in-mysql/