How to change collation of all tables in a database

Copy this snippet, replace database_name with the name of your database AND utf8_general_ci to any other collation you preferred. This will generate the sql commands needed for changing collation of all tables.

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1

Copy the output of the result and execute it in MySQL to change the collation.

Enjoy!