MySQL: Batch-Drop Tables

Dropping all tables of a given MySQL-Database is easy. We fetch all tablenames from the information_schema, concatenate them to a a series of “DROP TABLE”-Commands and pipe them to mysql for execution:

mysql -u root -p$MYSQL_PW -B -N -e "select concat('drop table $TARGETSCHEMA.',table_name,';') from information_schema.tables where table_schema='$TARGETSCHEMA'" information_schema | mysql -u root -p$MYSQL_PW $TARGETSCHEMA

You replace $MYSQL_PW with your MySQL-Root-Password and $TARGETSCHEMA with your target-database. If not root, you can also use another user-account with the necessary privileges.

-B stands for batch-mode. (can also be omitted)
-N suppresses the column-names of the result-set.
-e executes the following SQL.

A handy shell-script for our matter could look like this:

#!/bin/bash
# drop all schema-tables
# 2014-07-11 Marc Tempel

if [ $# -ne 2 ]
then
	echo "usage: $0 <targetschema> <mysql_root_pwd>"
	exit
fi

export MYSQL_PW=$2
TARGETSCHEMA=$1
TCOUNT=`mysql -u root -p$MYSQL_PW -B -N -e "select count(*) from information_schema.tables where table_schema='$TARGETSCHEMA'" information_schema`

read -p "Dropping all $TCOUNT tables of schema $TARGETSCHEMA. This can't be undone!"

mysql -u root -p$MYSQL_PW -B -N -e "select concat('drop table $TARGETSCHEMA.',table_name,';') from information_schema.tables where table_schema='$TARGETSCHEMA'" information_schema | mysql -u root -p$MYSQL_PW $TARGETSCHEMA

#EOF
Advertisements