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.

About these ads

~ by Azeem on August 11, 2007.

16 Responses to “Mysql dropping multiple tables.”

  1. Excellent thinking.

  2. Or you could try this:

    mysql -s -u webmaster -p web -e “show tables like ‘stup_%’” |xargs mysqldump -u webmaster -p web

  3. I mean, you could use another mysql command or mysqldump or whatever in the second command.

  4. Yeah there are multiple way i have seen another workaround on php as well.. about this thing!

  5. 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.

  6. True.

  7. 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

  8. 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 ;

  9. You can also try this:
    http://www.gottolearn.com/2009/08/26/dropping-multiple-tables-using-wildcard-in-mysql/

  10. Thanks this is great!

  11. Or just
    select concat(‘DROP TABLE ‘, table_name,’;’) from information_schema.tables where table_name like ‘jos_%’;

    Right click! and FIRE!

  12. [...] 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 [...]

  13. 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

  14. 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.

  15. 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 “@@”

  16. Just install sequel pro, connect and select the tables visually and click delete. Easy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: