List databases, tables, columns

List database table, column, character set, collation, data type, and more with this MySQL query.

SELECT
  t.table_schema AS 'Database',
  t.table_name AS 'Table',
  t.table_type AS 'Table Type',
  c.column_name AS 'Column',
  c.data_type AS 'Data Type',
  c.COLUMN_KEY AS 'Column Key',
  c.COLLATION_NAME AS 'Collation',
  c.CHARACTER_SET_NAME AS 'Character Set'
FROM
  information_schema. TABLES t
JOIN information_schema. COLUMNS c ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE
  t.table_schema NOT IN ('mysql', 'information_schema')
ORDER BY
  t.table_schema,
  t.table_type,
  t.table_name,
  c.ordinal_position;