Finding total number of rows of tables across multiple databases

Question

I have 67 different databases and each database has more than one common table. One such table is company and I want to find total rows in that table from all the databases. How can I write a query to get total number of rows from all the databases?

Answer 1

You can query the INFORMATION SCHEMA.

SELECT SUM(table_rows) FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'company'

You can use the ENGINE column in the table to see if you're counting on MyISAM tables (the table_rows will be correct then), or InnoDB (table_rows will be the estimate used by the optimizer)

If you have more than only the company table, you can GROUP BY tablename :)