How to quickly retrieve the table with the most rows in MySQL?

Question

The database is on a remote Linux server, I am using Windows.

I want to retrieve the table with the most rows in MySQL, but I am on a Windows client.

I have about 200 tables. I have to click their table name one by one to figure out the row count.

The databases has many tables, I can get their rows by executing

select count(*) from table

This will retrieve the rowcount one by one.

My Question

Is there a quick method to get the table with the most rows in MySQL workbench on Windows?

Answer 1

how about:

SELECT TABLE_NAME
      ,TABLE_ROWS 
  FROM INFORMATION_SCHEMA.TABLES 
 ORDER BY TABLE_ROWS DESC

Answer 2

SELECT  MAX(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'db_name'

=> This will return the maximum number of rows a table have.

SELECT  table_name, MAX(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'db_name'

=> This will return the maximum number of rows a table have with the table name

Quick Links

1. The INFORMATION_SCHEMA TABLES Table
2. Get record counts for all tables in MySQL database

Answer 3

SELECT TABLE_ROWS, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '{{schema_name}}'
ORDER BY TABLE_ROWS DESC
LIMIT 1;

This will tell you the table name with most number of rows. Replace schema_name with your database before executing query.