How do I order all tables by number of records in each

By the looks of it just doing a single SQL query won’t solve my problem, I was wondering if anyone knew of a way to order all tables in my database by the number of records in each. This doesn’t necessarily have to display on the front end. I was hoping I could do this in phpmyadmin, but can’t find a way.

I see that the query SHOW TABLES doesn’t allow ordering. Maybe there’s a way in PHP?

SELECT tablename
     , rows
  FROM ( SELECT 'table1' AS tablename
              , COUNT(*) AS rows 
           FROM table1
         UNION ALL 
         SELECT 'tabletwo', COUNT(*) FROM tabletwo
         UNION ALL 
         SELECT 'bucklemy', COUNT(*) FROM bucklemy
         UNION ALL 
         SELECT 'shoe', COUNT(*) FROM shoe
         ) AS d
ORDER
    BY rows DESC

:cool:

show table status;

will give you a list of all tables and other info, including number of Rows etc.

You will need to do an array sort if this resultset is built into an array.

SHOW TABLE STATUS gives accurate row counts only for some table types

:slight_smile: