Convert All Tables Collation

Description

Generate an ALTER TABLE statement for every base table in a schema, to bring an inconsistent database onto one collation. Produces the SQL — it doesn't run it.

Snippet

SELECT
  CONCAT( "ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME, " COLLATE utf8mb4_general_ci;" ) AS ExecuteTheString
FROM
  INFORMATION_SCHEMA.TABLES
WHERE
  TABLE_SCHEMA = "pro_axistms_com"
  AND TABLE_TYPE = "BASE TABLE";

Copy the result column and run it as a second pass.

Notes

Swap pro_axistms_com for the target schema and utf8mb4_general_ci for the collation you want.

TABLE_TYPE = "BASE TABLE" is what keeps views out of the results — views have no collation to alter.

Important caveat: ALTER TABLE ... COLLATE sets the table's default for future columns. It does not convert columns that already exist, so a table full of latin1_swedish_ci varchars stays that way and you get a database that looks converted but isn't. To rewrite the existing column data, generate this instead:

CONCAT( "ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME, " CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" )

CONVERT TO rewrites every text column, so it's slow on large tables and locks them. Back up first, and check for indexes near the 767/3072-byte key-length limit, since widening to 4-byte characters can push them over.