List tables with the largest number of columns in MySQL database

The query below returns tables from the MySQL database with the number of columns ordered by the ones that have more.


select col.table_schema as database_name,
        col.table_name as 'table', 
        count(*) as 'columns'
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'
where col.table_schema not in ('information_schema', 
        'performance_schema', 'sys', 'mysql')
--  and col.table_schema = 'your database name'
group by col.table_schema,
order by count(*) desc;


  • database_name - name of the schema/database
  • table - name of the table and its database (schema)
  • columns - number of columns in the table


  • One row: represents one table in a database (schema)
  • Scope of rows: all tables in non system schemas or in particular schema if schema where clause is uncommented
  • Ordered by: number of columns in descending order - showing the tables that have more columns first


You can uncomment database/schema where clause and provide name to narrow results to specific schema.

and col.table_schema = 'your database name'

Sample results

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