-- Rename tabel
RENAME TABLE old_table TO new_table;

-- Atau dengan ALTER
ALTER TABLE old_table RENAME TO new_table;

-- Rename multiple tables sekaligus
RENAME TABLE 
    old_table1 TO new_table1,
    old_table2 TO new_table2,
    old_table3 TO new_table3;

-- Pindahkan tabel ke database berbeda
RENAME TABLE old_db.old_table TO new_db.new_table;

-- Swap dua tables (berguna untuk maintenance)
RENAME TABLE 
    users TO users_old,
    users_new TO users;

-- MySQL 8.0+
ALTER TABLE table_name 
RENAME COLUMN old_column TO new_column;

-- Semua versi
ALTER TABLE table_name 
CHANGE COLUMN old_column new_column VARCHAR(100);

-- Dengan modify type juga
ALTER TABLE table_name 
CHANGE COLUMN old_name new_name VARCHAR(50) NOT NULL;

-- Multiple ALTER statements
ALTER TABLE users 
RENAME COLUMN first_name TO fname,
RENAME COLUMN last_name TO lname;

# 1. Backup database lama
mysqldump old_database > old_database_backup.sql

# 2. Create database baru
mysql -e "CREATE DATABASE new_database;"

# 3. Restore ke database baru
mysql new_database < old_database_backup.sql

# 4. Verify
mysql -e "SHOW TABLES FROM new_database;"

# 5. Drop database lama (setelah verify)
mysql -e "DROP DATABASE old_database;"

-- Rename semua tables ke database baru
RENAME TABLE 
    old_db.table1 TO new_db.table1,
    old_db.table2 TO new_db.table2,
    old_db.table3 TO new_db.table3;

-- Drop database kosong
DROP DATABASE old_db;

# Selalu backup sebelum rename
mysqldump database_name > backup_before_rename.sql

-- Cek foreign keys
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'database_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;

-- Cek stored procedures dan views
SHOW CREATE VIEW view_name;
SHOW CREATE PROCEDURE procedure_name;

# Update application code
# - SQL queries
# - ORM models
# - Migration scripts

# Search dalam codebase
grep -r "old_table_name" /path/to/application/

#!/bin/bash
# safe_rename_table.sh

DB_NAME="$1"
OLD_NAME="$2"
NEW_NAME="$3"

if [ -z "$DB_NAME" ] || [ -z "$OLD_NAME" ] || [ -z "$NEW_NAME" ]; then
    echo "Usage: $0 <database> <old_table> <new_table>"
    exit 1
fi

echo "=== SAFE TABLE RENAME ==="
echo "Database: $DB_NAME"
echo "From: $OLD_NAME"
echo "To: $NEW_NAME"

# 1. Backup
echo "Creating backup..."
mysqldump "$DB_NAME" "$OLD_NAME" > "/tmp/${OLD_NAME}_backup_$(date +%s).sql"

# 2. Verify table exists
TABLE_EXISTS=$(mysql -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_NAME = '$OLD_NAME';" | tail -1)
if [ "$TABLE_EXISTS" -eq 0 ]; then
    echo "Table $OLD_NAME does not exist!"
    exit 1
fi

# 3. Check for dependencies
echo "Checking dependencies..."
DEPS=$(mysql -e "SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '$OLD_NAME';" | tail -1)
if [ "$DEPS" -gt 0 ]; then
    echo "WARNING: Table has $DEPS foreign key dependencies!"
    read -p "Continue anyway? (yes/no): " confirm
    if [ "$confirm" != "yes" ]; then
        exit 1
    fi
fi

# 4. Perform rename
echo "Renaming table..."
mysql -e "RENAME TABLE \`$DB_NAME\`.\`$OLD_NAME\` TO \`$DB_NAME\`.\`$NEW_NAME\`;"

if [ $? -eq 0 ]; then
    echo "Rename successful!"
    echo "Backup: /tmp/${OLD_NAME}_backup_$(date +%s).sql"
else
    echo "Rename failed!"
    exit 1
fi

-- Jika rename gagal karena foreign key
-- 1. Drop FK constraint
ALTER TABLE child_table DROP FOREIGN KEY fk_constraint;

-- 2. Rename
RENAME TABLE old_table TO new_table;

-- 3. Recreate FK dengan nama baru
ALTER TABLE child_table 
ADD CONSTRAINT fk_new_table 
FOREIGN KEY (parent_id) REFERENCES new_table(id);

-- Jika column digunakan dalam views
-- 1. Drop views
DROP VIEW view_name;

-- 2. Rename column
ALTER TABLE table_name RENAME COLUMN old TO new;

-- 3. Recreate views
CREATE VIEW view_name AS SELECT new_column FROM table_name;