
SELECT
    CONCAT(
        'ALTER TABLE `', c.TABLE_SCHEMA,
        '`.`', c.TABLE_NAME,
        '` CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_spanish_ci,', -- Base ALTER TABLE statement with schema and table collation
        ' CHANGE COLUMN `', c.COLUMN_NAME,
        '` `', c.COLUMN_NAME, 
        '` ', c.COLUMN_TYPE, -- This includes VARCHAR(X), TEXT, etc.
        -- ' CHARACTER SET ''utf8mb4'' COLLATE ''utf8mb4_spanish_ci''',
        CASE WHEN c.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END,
        CASE
            WHEN c.COLUMN_DEFAULT IS NOT NULL AND c.COLUMN_DEFAULT != '' THEN CONCAT(' DEFAULT ''', REPLACE(c.COLUMN_DEFAULT, '''', ''''''), '''')
            WHEN c.COLUMN_DEFAULT IS NULL AND c.IS_NULLABLE = 'NO' THEN ' DEFAULT \'\' '
            ELSE ''
        END,
        ';' -- End of the statement for this specific column
    ) AS `alter_column_statement`
FROM information_schema.COLUMNS AS c
WHERE c.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') -- Exclude system databases
    AND c.DATA_TYPE IN ('varchar', 'text', 'tinytext', 'mediumtext', 'longtext') -- Consider common text types
    AND c.CHARACTER_SET_NAME IS NOT NULL
    AND c.CHARACTER_SET_NAME != 'utf8mb4'
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME;
