Query num_rows, size, num_tables, default_charset, default_collation for all databases

Question

Right now I've been able to come up with this:

SELECT
    table_schema AS 'name_db', 
    sum(table_rows) AS 'num_rows_db', 
    SUM(data_length + index_length) / 1024 / 1024 AS 'size_db(MB)', 
    count(*) as 'num_tables_db' 
FROM
    information_schema.TABLES
GROUP BY table_schema;

And now I'm trying to figure out how to add the charset and collation for all the databases but I'm not sure how to do it.

I've figured out information_schema.SCHEMATA table has DEFAULT_CHARACTER_SET_NAME and DEFAULT_COLLATION_NAME columns and I'd like to add those to my above query. Also, I'd like to know whether there is any easy way to display the db size in Kb or MB depending the size (ie: like heidiSQL summary does).

Guess I need to make some sort of join between these 2 tables using SCHEMA_NAME but the group by of my first query confuses me :/

Answer 1

It turns out it is best to do the aggregates and GROUP BY separately for each of the two tables needed.

SELECT  *
    FROM  
    (
        SELECT  table_schema AS 'name_db',
                sum(table_rows) AS 'num_rows_db',
                IF(SUM(data_length + index_length) > 1048576,
                    CONCAT(ROUND(SUM(data_length + index_length) / 1024 / 1024), ' MB'),
                    CONCAT(ROUND(SUM(data_length + index_length) / 1024), ' KB') ) AS 'size_db',
                count(*) as 'num_tables_db'
            FROM  information_schema.TABLES AS t
            GROUP BY  TABLE_SCHEMA 
    ) AS t
    JOIN  
    (
        SELECT  table_schema AS 'name_db',
                GROUP_CONCAT(DISTINCT CHARACTER_SET_NAME) AS charsets,
                GROUP_CONCAT(DISTINCT COLLATION_NAME) AS collations
            FROM  information_schema.COLUMNS
            GROUP BY  TABLE_SCHEMA
    ) AS c USING (name_db);

(There is some potential for users to be confused by table_schema vs name_db. Since one is the alias for the other, the two are interchangeable in some cases.)

You may want to tack ORDER BY name_db at the end. You may want to say `WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') to avoid the system tables.

Note that table_rows is only approximate in the case of InnoDB.