Sports

https://www.espncricinfo.com/ci/engine/match/scores/desktop.html
Tuesday, September 20, 2022

MySQL tables with row count

 



MySQL table row count information_schema

Overview


We can use Workbench or SQLYog or Putty prompt for connecting with our MySQL database.
My test database name is 'ABC' and I will connect through this with this below command.

 /opt/lampp/bin/mysql -uroot -p abc 
Note: We can skip the database name and connect to the database

(see diagram 1.1)
I am using Putty to connect to the MySQL database as below:-

Diagram 1.1



Finding Row count with Database Tables

Here is the SQL Query for finding MySQL database Schema with their Table Names and Row Count.

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,SUM(TABLE_ROWS) FROM
            INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA='abc'
            GROUP BY TABLE_NAME,TABLE_SCHEMA ORDER BY 1;


Second Part of the attached image



Conclusion


The above SQL Query has returned 82 Tables with their Schema name and Row Count.
We can use this query after Data Migration especially from on-prem to AWS Cloud environment using Workbench.

Popular