-- Specify database name filter and it is grouped by table_name
-- Ouput will get Table name and no of columns in each table
SELECT
TABLE_NAME,
COUNT(*) AS NO_OF_COLUMNS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = "database_name"
GROUP BY TABLE_NAME;
Output
TABLE_NAME
NO_OF_COLUMNS
userinfo
12
account_info
8
transaction_details
20
Order by no of columns count
-- Ascending
SELECT
TABLE_NAME,
COUNT(*) AS NO_OF_COLUMNS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = "database_name"
GROUP BY TABLE_NAME
ORDER BY COUNT(*) ASC;
-- Descending
SELECT
TABLE_NAME,
COUNT(*) AS NO_OF_COLUMNS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = "database_name"
GROUP BY TABLE_NAME
ORDER BY COUNT(*) DESC;
