MySQL: Get number of rows of table with specific name for all databases

Question

I am trying to build a mysql query (I want to use it in phpMyAdmin) to get the number of rows of all tables where the table name contains the word "user" or "member". But instead of questioning a single db, I want to run this on all dbs. As a result, i want to have a table with the columns: DB name, Table Name, Numer of Rows.

Any tips to achieve that would be much appreciated!

Answer 1

You don't need to query all databases, and anyway you can't. You need to query only one database called information_schema, which contains all metadata about your databases (table structures, etc).

Here is the query you want:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
    FROM information_schema.TABLES
    WHERE
          TABLE_NAME LIKE '%user%'
          OR TABLE_NAME LIKE '%member%';

Notes:

• You don't need any permission on information_schema. But if there is some table you can't access (because you don't have permissions) you will not see it in the results.
• The number of rows is MySQL estimation. It is never exact, sometimes it is even inaccurate. If this is not enough for you, you will need to run a query for each table to count the rows. There is no way to get the actual row count with 1 query.
• I write the name of system databases, tables and columns with their actual case (information_schema is written lower case). For some reason, this is different than the convention used by the documentation and most technical articles (INFORMATION_SCHEMA). Just use the practice you prefer.