Average number of columns per table in MariaDB 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 the specified database (schema)
  • tables - number of tables in the specified database (schema)
  • average_column_count - average number of columns per table in the specified database (schema)


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

Sample results

There are no comments. Click here to write the first comment.