Finding total number of rows of tables across multiple databases

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 fro开发者_Go百科m all the databases?

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 :)

You can get this from the INFORMATION_SCHEMA. If you are using the MyISAM storage engine this will give you exact row counts. If you use InnoDB the counts will be approximate.

Here's an example query to get the total row counts for all tables named "company" across all schemas:

select sum(table_rows) 
from information_schema.tables 
where table_name = 'company';