CMS-Experte | IT-Consultant in Berlin

Drop multiple tables of a MySQL database

#!/usr/bin/perl -w
#
# Author: Ramiro Gomez
#
# $Id: drop_mysql_tables.pl,v 1.8 2004/11/08 23:25:18 ramiro Exp
#
# Use this Perl script to drop all tables or tables matching a 
# Perl regular expression of the MySQL database specified on the
# command line. This is useful if you don't have the permissions
# for dropping and creating databases.
use strict;
use DBI;
use Getopt::Std;
use Term::ReadKey;

usage() unless (@ARGV);

our ($opt_u, $opt_h, $opt_v, $opt_r,
     $db_pass, $host_name, $db_user, $db_name, $regex);
getopts("vu:r:h:");

if ($opt_u) {
    $db_user = $opt_u;
} else { usage() };

if ($opt_h) {
    $host_name = $opt_h;
} else {
    $host_name = 'localhost';
}

$db_name = shift || usage();

print "Enter password: ";
ReadMode('noecho');
$db_pass = ReadLine(0);
ReadMode('restore');
chomp $db_pass;
print "\n";

my $dsn = "DBI:mysql:host=$host_name;database=$db_name";
my $dbh = DBI->connect( $dsn, $db_user, $db_pass );
print "Connected to database: $db_name at $host_name\n" if $opt_v;

my @tables = @{ $dbh->selectcol_arrayref ("SHOW TABLES") };

if ($opt_r) {
    $regex = qr($opt_r);
    @tables = grep {/$regex/} @tables;
}

map {
    $dbh->do("DROP table $_");
    print "Dropped table: $_\n" if $opt_v;
} @tables;

$dbh->disconnect;
print "Disconnected from database: $db_name at $host_name\n" if $opt_v;

sub usage {
    die <<"EOF";
Usage:
$0 [-v] [-h hostname] [-r regex] -u username database
Examples:
$0 -u user -h hostname database
$0 -v -u user database
$0 -v -u user -r 'regex' database

User and database are mandatory arguments.
If no hostname is supplied localhost will be used.
If v is supplied messages will be printed.
If r is supplied table names are matched
against it's value, a Perl regular expression.
If the name matches the table will be deleted.
$!
EOF
}