SQL query to list numbers of rows in each table in a db schema

get_tables_counts.sql

# tested on Mysql 5.7-8

# set database schema name to run against
SET @db_name = "DBNAME";

# actual query, two columns resulted: table name and its rows count
SELECT
    TABLE_NAME,
    SUM(TABLE_ROWS) as c
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = @db_name
group by
    TABLE_NAME
order by
    c;