I wrote a bash shell script that deletes all the tables in a mysql database and I thought I’d just post it here in case anyone might find it useful. It accepts one required argument which is the database name and one optional argument which is the username with DROP/CREATE DATABASE privileges to dbname .

#!/bin/bash
if [[ ${1} == '' ]]
then
echo “”
echo ” `basename ${0}` {dbname} [{user}]”
echo “”
echo ” {dbname} – is the name of the mysql database to zap”
echo ” {user} – optional, user ‘root’ is used as default”
echo “”
exit 1;
else
read -p “Are you sure you want to empty ${1} [Y/N]? ” ans
if [[ ${ans} == "y" || ${ans} == "Y" ]]
then
user=${2:-”root”}
echo -n “Using mysql user ‘${user}’, ”
mysql -u ${user} -p -e “drop database ${1};create database ${1};” && echo “${1} emptied.”
exit 0;
else
echo “Aborted.”
exit 1;
fi
fi

Note: Oh and be careful in using the script ‘coz it will erase all the data in your database so make sure you have backups.

This morning I was doing some tests and I found myself having to use a web-based mysql tool called phpmyadmin to delete all the tables in a mysql database.

Since I was to do it several times, I decided to use the command line interface program mysql. I was kinda surprise that I didn’t find any single command to drop all the tables, anyway deleting all the tables can be done by issuing the following command:

sh$ mysql -u username -p -e “drop database dbname; create database dbname;”

You will need to specify a username with drop/create privileges for the dbname database. It will then prompt for the user’s password when you hit enter on the command line.