Average number of columns per table in MySQL database

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


select database_name,
    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 - 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)


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

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.