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.