Inflexible Schema Migration

-- Check large tables for migration risk
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb,
    TABLE_ROWS,
    CASE 
        WHEN (DATA_LENGTH + INDEX_LENGTH) > 10737418240 
        THEN 'HIGH RISK - Use pt-online-schema-change or gh-ost'
        WHEN (DATA_LENGTH + INDEX_LENGTH) > 1073741824 
        THEN 'MEDIUM RISK - Test ALTER time'
        ELSE 'LOW RISK'
    END as migration_risk
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

-- Check MySQL version for online DDL support
SELECT 
    VERSION() as mysql_version,
    CASE
        WHEN VERSION() LIKE '%8.0%' THEN 'Good online DDL support with INSTANT algorithms'
        WHEN VERSION() LIKE '%5.7%' THEN 'Limited online DDL, consider pt-online-schema-change'
        ELSE 'Poor online DDL support, external tools required'
    END as ddl_capability;

-- Check for tables with many indexes (slower ALTERs)
SELECT 
    TABLE_NAME,
    COUNT(*) as index_count,
    GROUP_CONCAT(INDEX_NAME) as indexes
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME
HAVING COUNT(*) > 5
ORDER BY index_count DESC;

-- Check large tables for migration complexity
SELECT TOP 20
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS RowCount,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    CASE 
        WHEN SUM(a.total_pages) * 8 / 1024 > 10240 
        THEN 'HIGH RISK - Plan for extended downtime'
        WHEN SUM(a.total_pages) * 8 / 1024 > 1024 
        THEN 'MEDIUM RISK - Test ALTER duration'
        ELSE 'LOW RISK'
    END as migration_risk
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY s.name, t.name
ORDER BY TotalSpaceMB DESC;

-- Check SQL Server edition for online operations
SELECT 
    SERVERPROPERTY('Edition') as sql_edition,
    CASE
        WHEN SERVERPROPERTY('Edition') LIKE '%Enterprise%' 
        THEN 'Supports online index operations'
        ELSE 'Limited online operations - plan for downtime'
    END as online_capability;

-- Find tables with many constraints (complex migrations)
SELECT 
    t.name AS TableName,
    COUNT(DISTINCT c.constraint_id) as constraint_count,
    STRING_AGG(c.name, ', ') as constraints
FROM sys.tables t
JOIN sys.check_constraints c ON t.object_id = c.parent_object_id
GROUP BY t.name
HAVING COUNT(DISTINCT c.constraint_id) > 3
ORDER BY constraint_count DESC;