Sometimes if you have complicated constraints drop of all tables is not successful even with CASCADE. To avoid these funny problems I prepared this script to drop constraints on table first and then drop table with cascade. Use it with great caution!!!

Command line parameters: user password schema_name


#!/bin/bash

MYUSER=$1
MYPASS=$2
MYSCHEMA=$3

# if debug=1 script will only write out texts of mysql statements and not perform them
debug=0

for tablename in $(mysql -u $MYUSER -p$MYPASS -D $MYSCHEMA -e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$MYSCHEMA'" -Ns 2>/dev/null); do
echo $tablename
droptable="DROP TABLE ${MYSCHEMA}.${tablename} CASCADE;"

for constr in $(mysql -u $MYUSER -p$MYPASS -D $MYSCHEMA -e "SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='$MYSCHEMA' AND TABLE_NAME='$tablename'" -Ns 2>/dev/null); do
echo "constraint: $constr"
dropconstr="ALTER TABLE ${MYSCHEMA}.${tablename} DROP FOREIGN KEY ${constr};"
[ "$debug" == "1" ] && echo $dropconstr

[ "$debug" == "0" ] && mysql -u $MYUSER -p$MYPASS -D $MYSCHEMA -e "$dropconstr" 2>/dev/null
done

[ "$debug" == "1" ] && echo $droptable
[ "$debug" == "0" ] && mysql -u $MYUSER -p$MYPASS -D $MYSCHEMA -e "$droptable" 2>/dev/null

echo ""
done