sql_drop_tables.sh

#!/bin/bash
# Script Version: 02
# Description: Drops all tables in a specified MySQL database. If only one argument is given, the user and database name will be the same.

# Check arguments
if [ $# -lt 1 ]; then
    echo "Usage: $0 <DB_USER> [DB_NAME]"
    exit 1
fi

# Assign arguments
DB_USER=$1
DB_NAME=${2:-$1} # If no second argument is provided, use the first as the database name.

# Prompt for password
read -sp "Enter MySQL password: " DB_PASS
echo

# Confirmation prompt
read -p "Are you sure you want to drop all tables in $DB_NAME? (y/n): " CONFIRM
if [[ "$CONFIRM" != "y" ]]; then
    echo "Operation cancelled."
    exit 0
fi

# Disable foreign key checks
mysql -u "$DB_USER" -p"$DB_PASS" -e "SET FOREIGN_KEY_CHECKS = 0;" "$DB_NAME"
if [ $? -ne 0 ]; then
    echo "Error disabling foreign key checks. Exiting."
    exit 1
fi

# Get all table names
tables=$(mysql -u "$DB_USER" -p"$DB_PASS" -N -e "SELECT table_name FROM information_schema.tables WHERE table_schema = '$DB_NAME';")
if [ -z "$tables" ]; then
    echo "No tables found in $DB_NAME."
else
    # Drop each table
    for table in $tables; do
        mysql -u "$DB_USER" -p"$DB_PASS" -e "DROP TABLE IF EXISTS $table;" "$DB_NAME"
        if [ $? -eq 0 ]; then
            echo "Dropped table $table"
        else
            echo "Error dropping table $table"
        fi
    done
fi

# Enable foreign key checks
mysql -u "$DB_USER" -p"$DB_PASS" -e "SET FOREIGN_KEY_CHECKS = 1;" "$DB_NAME"

echo "All tables in database $DB_NAME have been processed."