1

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.

Realto619
  • 301
  • 3
  • 13

1 Answers1

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

KM11
  • 697
  • 6
  • 15