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.

Query

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,
    col.table_name
order by count(*) desc;

Columns

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

Rows

  • 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

Notes

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

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