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, col.table_name 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'