How can I check the character set of all the tables along with column names 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 Column_name, 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 along with the column names in a database named ‘Alpha’.

mysql> Select Column_name 'Column',TABLE_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns Where TABLE_SCHEMA = 'Alpha';

+---------+------------+--------------------+
| Column  | TABLE_NAME | CHARACTER_SET_NAME |
+---------+------------+--------------------+
| Name    | employee   | latin1             |
| email   | employee   | latin1             |
| Name    | student    | latin1             |
| RollNo  | student    | NULL               |
| Address | student    | latin1             |
+---------+------------+--------------------+
5 rows in set (0.04 sec)

Rishi Rathor

Published on 19-Feb-2018 12:52:31

• Related Questions & Answers
• How can we check the character set of all the tables in a particular MySQL database?
• How Can I check the size of the tables in a particular MySQL database?
• How can I check MySQL tables from a database in accordance with particular column/s name?
• How can I check the list of MySQL tables, in the current database we are using, along with table type in the result set?
• How can we check the default character sets of a particular MySQL database?
• How can I query for all the tables having a particular column name?
• How can I check the list of MySQL tables, in a different database than we are using currently, along with table type in the result set?
• How to check table status of the tables in a particular MySQL database?
• How can I check the list of MySQL tables, in a different database than we are using currently, along with table type in the result set using IN operator?
• How can I describe all tables in the database through a single statement in MySQL?
• How can I check the tables of databases other than current database?
• How can we see the list, along with complete information, of stored functions in a particular MySQL database?
• How can we see the list, along with complete information, of stored procedures in a particular MySQL database?
• How can we see the list, along with other information, stored procedures in a particular MySQL database?
• Get the names beginning with a particular character using LIKE in MySQL