How can we check the character set of all the tables in a particular MySQL database?

With the help of the following MySQL query we can check the character sets of all the tables in a particular database −

mysql> Select TABLE_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns Where TABLE_SCHEMA = 'db_name';

Example

For example, the query below returns the character sets of all the tables in a database named ‘Alpha’.

mysql> Select TABLE_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns Where TABLE_SCHEMA = 'Alpha';
+------------+--------------------+
| TABLE_NAME | CHARACTER_SET_NAME |
+------------+--------------------+
| employee   | latin1             |
| employee   | latin1             |
| student    | latin1             |
| student    | NULL               |
| student    | latin1             |
+------------+--------------------+
5 rows in set (0.06 sec)

The output shows 5 rows, 2 for employee table and 3 for student table because table employee contains two columns and table student contains three columns.