
SELECT
    CONCAT(
        'ALTER TABLE `', c.TABLE_SCHEMA,
        '`.`', c.TABLE_NAME, '` CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_spanish_ci;\n',
        ( -- Subconsulta para generar todas las cláusulas CHANGE COLUMN para la tabla actual
            SELECT
                GROUP_CONCAT(
                    CONCAT(
                        'ALTER TABLE `', c_inner.TABLE_SCHEMA, 
                        '`.`', c_inner.TABLE_NAME, '` CHANGE COLUMN `', c_inner.COLUMN_NAME, '` `', c_inner.COLUMN_NAME,
                        '` ', c_inner.COLUMN_TYPE,
                        ' CHARACTER SET ''utf8mb4'' COLLATE ''utf8mb4_spanish_ci''',
                        CASE
                            WHEN c_inner.IS_NULLABLE = 'NO' THEN ' NOT NULL'
                            ELSE ' NULL'
                        END,
                        CASE
                            WHEN c_inner.COLUMN_DEFAULT IS NOT NULL AND c_inner.COLUMN_DEFAULT != '' THEN CONCAT(' DEFAULT ''', REPLACE(c_inner.COLUMN_DEFAULT, '''', ''''''), '''')
                            WHEN c_inner.COLUMN_DEFAULT IS NULL AND c_inner.IS_NULLABLE = 'NO' THEN ' DEFAULT \'\' '
                            ELSE ''
                        END,
                        ';'
                    ) SEPARATOR '\n' -- Separador de nueva línea entre cada CHANGE COLUMN
                )
            FROM information_schema.COLUMNS AS c_inner
            WHERE c_inner.TABLE_SCHEMA = c.TABLE_SCHEMA
                AND c_inner.TABLE_NAME = c.TABLE_NAME
                AND c_inner.DATA_TYPE IN ('varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
                AND c_inner.CHARACTER_SET_NAME IS NOT NULL
                AND c_inner.CHARACTER_SET_NAME != 'utf8mb4'
        )
    ) AS `alter_table_statements`
FROM information_schema.COLUMNS AS c
WHERE c.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND c.DATA_TYPE IN ('varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
    AND c.CHARACTER_SET_NAME IS NOT NULL
    AND c.CHARACTER_SET_NAME != 'utf8mb4'
GROUP BY -- Agrupamos por esquema y tabla para generar una fila por cada tabla afectada
    c.TABLE_SCHEMA, c.TABLE_NAME
ORDER BY
    c.TABLE_SCHEMA, c.TABLE_NAME;