Average number of columns per table in MySQL database

The query below returns the average number of columns per table in a database (schema).

Query

select database_name,
    columns,
    tables,
    cast((1.0*columns/tables) as decimal(10,2)) as average_column_count
from (
    select col.table_schema as database_name,
        count(*)  as 'columns',
        count(distinct col.table_name) as 'tables'
    from information_schema.columns as col
    inner join information_schema.tables as tab
        on tab.table_schema = col.table_schema
            and tab.table_name = col.table_name
            and tab.table_type = 'BASE TABLE'
            and tab.table_schema not in ('mysql', 'information_schema',
                                         'performance_schema', 'sys')
    group by database_name
) as q
order by database_name;

Columns

  • columns - total number of columns in a database (schema)
  • tables - number of tables in a database (schema)
  • average_column_count - average number of columns in a table in a database (schema)

Rows

  • One row: represents a database (schema)
  • Scope of rows: all databases (schemas)
  • Ordered by: database_name

Sample results