Query to show all tables and their collation

Question

Is there a query that can be run in mysql that shows all tables and their default collation? Even better if there was on that could show all collations on all columns of all tables.

Answer 1

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

Answer 2

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_COLLATION FROM INFORMATION_SCHEMA.TABLES;