Web Apps Since 2004.

Is your website boring?  Kick it up a notch.  Encodable apps easily drop into your website to make it modern, fun, and interactive, with features like file uploads, user accounts, paid subscriptions, protected pages, live chat, visitor logging, mailing lists, and more.
All Encodable apps include:
• Easy setup
• Lifetime license
• Free tech support
• Full customizability

MySQL: Drop Multiple Tables From a Database

# Filed on Jul 9, 2009 by AnthonyDiSante reply

MySQL annoyingly provides no way to delete multiple tables matching a certain string.  For example, you can say "SELECT * FROM table WHERE name like ’foo%’", but there’s nothing similar for deleting tables.

So here’s a small simple Perl script to do just that.  Save it as droptables.pl, edit the 4 variables at the top, then run "perl droptables.pl".  Simple as that.


use strict;
use DBI;

my $hostname = '';
my $database = '';
my $username = '';
my $password = '';

my $dbh = DBI->connect("dbi:mysql:${database}:$hostname",
  $username, $password) or die "Error: $DBI::errstr\n";

my $sth = $dbh->prepare("SHOW TABLES");
$sth->execute or die "SQL Error: $DBI::errstr\n";
my $i = 0;
my @all_tables = ();
while(my $table = $sth->fetchrow_array)
  print "table $i: $table\n";
  push @all_tables, $table;
my $total_table_count = $i;

print "Enter string or regex to match tables to "
  . "delete (won't delete yet): ";
my $regex = <STDIN>;
chomp $regex;

$i = 0;
my @matching_tables = ();
foreach my $table (@all_tables)
  if($table =~ /$regex/i)
    print "matching table $i: $table\n";
    push @matching_tables, $table;
my $matching_table_count = $i;

  print "$matching_table_count out of $total_table_count "
    . "tables match, and will be deleted.\n";
  print "Delete tables now? [y/n] ";
  my $decision = <STDIN>;
  chomp $decision;

  $i = 0;
  if($decision =~ /y/i)
    foreach my $table (@matching_tables)
      print "deleting table $i: $table\n";
      my $sth = $dbh->prepare("DROP TABLE $table");
      $sth->execute or die "SQL Error: $DBI::errstr\n";
    print "Not deleting any tables.\n";
  print "No matching tables.\n";

Reply to this message here:

Your name
Email (why?)
Website (if you have one)
search posts:

home | archives ]

Shopping Cart

Client Quotes

You've done a wonderful job with FileChucker and UserBase, and they have made a big difference to how our website runs.
– Nicholas H.
I just installed the demo of your product and got it up and running in no time.  I searched high and low for a decent login script and thank God I found yours.
– Adrian F.
Do you know how rare it is to have a "canned" shopping cart that can easily do complex pricing options on a single item?  Basically, they don't exist!  I have looked.  Everywhere!  And the few that might even come close to CornerStore's functionality cost a fortune!
– Tashina P.
FileChucker is working great...  Clients love it.  Vendors love it.  We love it.
– Gerry W.
I looked all over trying to find a simple cgi script.  I found that FileChucker was by far the best.  If you have issues with your hosting service's php.ini max upload size then this is the way to go.  Looking forward to future enhancements.
– Bob C.
Thanks again for a great product and great support - beyond expectations.
– Greg S.
FileChucker is helping drive the backend of several high profile entertainment sites for people like Shania Twain and Dolly Parton.  We're also using it to drive backend file uploads for a multi-billion dollar banking institution.  It's a great product.  We've tried other "chucking" upload solutions with progress bars using flash and php, but nothing works as reliably as FileChucker.
– Michael W.
The amount of customization in the program is incredible.  I was able to integrate it into my existing page layout relatively simply.  I was also able to easily customize the look/feel to match the current site.
– Jason M.
The work, the thought and the organization you put into this app is incredible.
– Bruce C.
FileChucker is a great drop-in solution for file uploads, and worth every penny of its very reasonable cost.  Encodable's support is excellent to boot.
– Loren A.
Why didn't I just do this from the get-go?  So much easier.  Thanks for your work.  FileChucker makes my work easier.
– Dominic M.
I want to thank you for your efforts on Userbase. It has become an integral part of our business and has allowed us to branch out and begin using automation on a lot of our processes. Userbase has become the gateway to advancement for our company's processes for our clients and employees.
Our members think your software is fantastic...  I would recommend your software and your company to anyone.  Thanks for all your help.  It has been a pleasure dealing with you.
– Tommy A.
Just one word: Fantastic.  10-minute job to plug FileChucker into my app, and it now works a treat.  It's through the hard work by people like yourselves that make my job so much easier.  Congratulations on an outstanding product... Many many thanks.
– Sean F.
Thank you VERY much for all of your help.  You've really impressed me.  We have support agreements for other software that costs thousands of dollars / year (just for the support), and most of them aren't as helpful as you have been.
– Keith Y.
Nice script, it's saving the day on our project.
– Aaron W.
I just want to say you guys really stand alone in that you have a quality product and you provide genuine customer service.  It's sad but those qualities are seldom found separately, much less together.  Thanks again for your time and help.
– Alex S.
I just wanted to say that yours is the first product that I've tested so far that hasn't failed on handling uploads.  This is going to work for a print company, so they are handling nothing but large files and all the other solutions I've tried so far have not been reliable.  So far yours has been 100% successful in my tests.
– Kevin H.