SET @DBNAME = 'example';
-- requested charset
SET @CHSET = 'utf8mb4';
-- requested collation
SET @COLL = 'utf8mb4_general_ci';
-- DATABASE collation
SELECT CONCAT('ALTER DATABASE `',@DBNAME,'` CHARACTER SET ',@CHSET,' COLLATE ',@COLL,';') as _sql UNION ALL
-- TABLE collations
SELECT CONCAT('ALTER TABLE `', T.table_name, '` CHARACTER SET ',@CHSET,' COLLATE ',@COLL,';') as _sql FROM information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C JOIN information_schema.TABLES AS T ON T.table_collation = C.collation_name WHERE T.table_schema = @DBNAME AND (C.CHARACTER_SET_NAME != @CHSET OR C.COLLATION_NAME != @COLL) UNION ALL
-- VARCHAR and TEXT fields collations
SELECT CONCAT( 'ALTER TABLE `', c.table_name, '` MODIFY `', c.column_name, '` ', c.DATA_TYPE, IF(c.DATA_TYPE = 'varchar', CONCAT( '(', c.CHARACTER_MAXIMUM_LENGTH, ')'), ''), ' CHARACTER SET ',@CHSET,' COLLATE ', @COLL, (CASE WHEN c.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';' ) as _sql FROM information_schema.COLUMNS c JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE c.TABLE_SCHEMA = @DBNAME AND t.TABLE_TYPE LIKE '%TABLE%' AND (c.DATA_TYPE = 'varchar' OR c.DATA_TYPE LIKE '%text%') AND (c.CHARACTER_SET_NAME != @CHSET OR c.COLLATION_NAME != @COLL);`