mysql drop all tables views triggers routines events

mysql_drop_all_db_contents.sh

#!/usr/bin/env bash

set -eu

if [ $# -ne 1 ]
then
	echo "Usage: $0 {MySQL-Database-Name}"
	echo "Drops all tables, views, triggers, routines, and events from a MySQL database"
	echo "This script expects mysql client credentials to be supplied in a user's .my.cnf"
	exit 1
fi

DB_NAME="$1"
DB_OPTIONS="--batch --skip-column-names --default-character-set=utf8"

QUERY_INIT="
SET FOREIGN_KEY_CHECKS = 0;
SET @DATABASE_NAME_FOR_TABLE_DROP = '${DB_NAME}';
"


TABLE_QUERY=$(cat <<'EOSQL'
SELECT CONCAT('`', table_schema, '`', '.', '`', table_name, '`')
FROM information_schema.tables
WHERE table_schema = (SELECT @DATABASE_NAME_FOR_TABLE_DROP);
EOSQL
)
TABLES=$(echo "${QUERY_INIT} ${TABLE_QUERY}" | mysql -D ${DB_NAME} ${DB_OPTIONS})

for table in $TABLES
do
    echo "Deleting ${table} table from ${DB_NAME} database..."
    echo "${QUERY_INIT} DROP TABLE IF EXISTS ${table};" | mysql -D ${DB_NAME} ${DB_OPTIONS}
done


VIEW_QUERY=$(cat <<'EOSQL'
SELECT CONCAT('`', table_schema, '`', '.', '`', table_name, '`')
FROM information_schema.views
WHERE table_schema = (SELECT @DATABASE_NAME_FOR_TABLE_DROP);
EOSQL
)
VIEWS=$(echo "${QUERY_INIT} ${VIEW_QUERY}" | mysql -D ${DB_NAME} ${DB_OPTIONS})

for view in $VIEWS
do
    echo "Deleting ${view} view from ${DB_NAME} database..."
    echo "${QUERY_INIT} DROP VIEW IF EXISTS ${view};" | mysql -D ${DB_NAME} ${DB_OPTIONS}
done


TRIGGER_QUERY=$(cat <<'EOSQL'
SELECT CONCAT('`', trigger_schema, '`', '.', '`', trigger_name, '`')
FROM information_schema.triggers
WHERE trigger_schema = (SELECT @DATABASE_NAME_FOR_TABLE_DROP);
EOSQL
)
TRIGGERS=$(echo "${QUERY_INIT} ${TRIGGER_QUERY}" | mysql -D ${DB_NAME} ${DB_OPTIONS})

for trigger in $TRIGGERS
do
    echo "Deleting ${trigger} trigger from ${DB_NAME} database..."
    echo "${QUERY_INIT} DROP TRIGGER IF EXISTS ${trigger};" | mysql -D ${DB_NAME} ${DB_OPTIONS}
done


ROUTINE_QUERY=$(cat <<'EOSQL'
SELECT CONCAT('`', routine_schema, '`', '.', '`', routine_name, '`')
FROM information_schema.routines
WHERE routine_schema = (SELECT @DATABASE_NAME_FOR_TABLE_DROP);
EOSQL
)
ROUTINES=$(echo "${QUERY_INIT} ${ROUTINE_QUERY}" | mysql -D ${DB_NAME} ${DB_OPTIONS})

for routine in $ROUTINES
do
    echo "Deleting ${routine} routine/procedure from ${DB_NAME} database..."
    echo "${QUERY_INIT} DROP PROCEDURE IF EXISTS ${routine};" | mysql -D ${DB_NAME} ${DB_OPTIONS}
done


EVENT_QUERY=$(cat <<'EOSQL'
SELECT CONCAT('`', event_schema, '`', '.', '`', event_name, '`')
FROM information_schema.events
WHERE event_schema = (SELECT @DATABASE_NAME_FOR_TABLE_DROP);
EOSQL
)
EVENTS=$(echo "${QUERY_INIT} ${EVENT_QUERY}" | mysql -D ${DB_NAME} ${DB_OPTIONS})

for event in $EVENTS
do
    echo "Deleting ${event} event from ${DB_NAME} database..."
    echo "${QUERY_INIT} DROP EVENT IF EXISTS ${event};" | mysql -D ${DB_NAME} ${DB_OPTIONS}
done