MySQL: Script to find default Character Set for Database, Table and Column

In this post, I am sharing few scripts for finding the default character set of a MySQL Database, Table and Column.

Generally, during the process of database migration, we are checking default character set and collation at MySQL Server level.

But in our production MySQL Server, I found that some of the tables have different character set which was not required.

As a database professional, we have to maintain consistency between all database objects.

Find default Characater Set for Database:

1 2 3 4

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = 'Database_Name';

Find default Characater Set for Table:

1 2 3 4 5 6 7

SELECT CCSA.character_set_name FROM information_schema.TABLES AS T INNER JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA ON CCSA.collation_name = T.table_collation WHERE T.table_schema = 'Database_Name'   AND T.table_name = 'Table_Name';

Find default Character Set for Column:

1 2 3 4 5 6

SELECT character_set_name FROM information_schema.COLUMNS WHERE table_schema = 'Database_Name'   AND table_name = 'Table_Name'   AND column_name = 'Column_Name';