[How To] Change character set and collate for all tables (and fields)

This is the template:

SELECT CONCAT(
  "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " CHARACTER SET [CHARACTER-SET] COLLATE [COLLATE];",
  "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " CONVERT TO CHARACTER SET [CHARACTER-SET] COLLATE [COLLATE];"
) AS queries
FROM information_schema.TABLES WHERE TABLE_SCHEMA = '[DBNAME]';

Running this query will produce a list of ALTER TABLE queries you can adjust (remove/modify lines for some tables etc.).
Just replace [CHARACTER-SET], [COLLATE] and [DBNAME] with values you want to set.

If you want to run the code right away just modify statement like this:

SELECT CONCAT(
  "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " CHARACTER SET [CHARACTER-SET] COLLATE [COLLATE];",
  "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " CONVERT TO CHARACTER SET [CHARACTER-SET] COLLATE [COLLATE];"
) INTO @queries
FROM information_schema.TABLES WHERE TABLE_SCHEMA = '[DBNAME]';
PREPARE stmt1 FROM @queries;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

For example below you can find the code which converts exampledb into the UTF-8 charset with utf8_unicode_ci collate.

SELECT CONCAT(
  "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
  "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME, " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
) INTO @queries
FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'exampledb';
PREPARE stmt1 FROM @queries;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

You can use this CodePen for generate snippet for you.