« previous: How To Rename An Xcode Project | next: Encodable.com: One Million Visitors Served! »
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.
#!/usr/bin/perl 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) { $i++; 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) { $i++; print "matching table $i: $table\n"; push @matching_tables, $table; } } my $matching_table_count = $i; if($matching_table_count) { 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) { $i++; print "deleting table $i: $table\n"; my $sth = $dbh->prepare("DROP TABLE $table"); $sth->execute or die "SQL Error: $DBI::errstr\n"; } } else { print "Not deleting any tables.\n"; } } else { print "No matching tables.\n"; }