Just started working with Magento and I wanted to get the number of fields in each table

Question

I'm astounded by the number of tables in Magento Enterprise 1.13 (over 200). I'm trying to get a handle on the way things are organized and I think it would be helpful to know the number of columns in each of the tables. The following query will get me a breakdown of columns and their data_types for each table:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = `<database_name>`
ORDER BY TABLE_NAME;

But I would also like to know the number of columns in each table.

SELECT COUNT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = '<database_name>'
AND TABLE_NAME IN (
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = '<database_name>'
);

Unfortunately, the above query returns a count of the total number of columns in the database. I realize that my approach is too simplistic and a LOOP or a FOREACH statement is closer to the solution I'm looking for but I don't know how to make the leap to that point.

Answer 1

SELECT TABLE_NAME, COUNT(COLUMN_NAME) AS NoCOLUMNS
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = '<database_name>'
    AND TABLE_NAME IN (SELECT TABLE_NAME
                       FROM INFORMATION_SCHEMA.TABLES 
                       WHERE TABLE_SCHEMA = '<database_name>')
GROUP BY TABLE_NAME;

Basic GROUP BY DEMO