Query to show all tables and their collation

mysql schema collation

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME    FROM INFORMATION_SCHEMA.COLUMNS

mysql schema collation

Bear in mind that collation can be defined to tables and also to columns.

A column's collation might be different to its parent table. Here is a query to get the collation from tables (not columns)

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATIONFROM INFORMATION_SCHEMA.TABLES;

mysql schema collation

Output information(status) about all tables in the database as "phpmyadmin":

SHOW TABLE STATUS FROM your_db_name;

CodeHunter