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/
Thanks this is great!
Or just
select concat(‘DROP TABLE ‘, table_name,’;') from information_schema.tables where table_name like ‘jos_%’;
Right click! and FIRE!
[...] out Mysql dropping multiple tables for a good desciption on how to do it using command line tools (if you can connect to the server [...]
Mass dropping of mysql tables said this on February 7, 2012 at 12:25 am |
Same here:
for T in `echo “show tables” | mysql -u USER -pPASSWD DB | grep “^PREFIX”`; do echo “DROP TABLE $T;” | mysql -u USER -pPASSWD DB; done
Thanks for this nice post, it was really helpful! My only suggestion is to turn result.txt into the mysql-readable script with a single sed command:
sed ‘s/$/,/;$s/.$/;/;1d;2i\DROP TABLE’ result.txt > alltables.sql
This adds a comma to the end of each line, then replaces the last comma with a semicolon, and finally replaces the first line with DROP TABLE.
I also noticed that your gawk command for adding the commas didn’t work properly for me. A lot of the lines in the output file would be mangled. After replacing the $1 with $0, it worked correctly.
bash script that covers foreign key checks, group_concat limit and selective tables, one liner
mysql -u DB_USER -pDB_PASS -e ‘SET SESSION group_concat_max_len = 10000000; SELECT CONCAT(“SET FOREIGN_KEY_CHECKS=0;”, “DROP TABLE “, GROUP_CONCAT(table_name), “;”) FROM information_schema.tables WHERE table_schema = “DB_NAME” and table_name like “%”‘ -s | xargs -I \ “@@” mysql -u DB_USER -pDB_PASS -D DB_NAME -e “@@”
Just install sequel pro, connect and select the tables visually and click delete. Easy.