How to select all data from specific tables for a given schema/prefix in MySQL Database

Question

I have a muliti-tenant database structure where each tenant resides in a separate database.

So what I had wanted is to create a procedure that spools data of particular tables from all these databases having the same prefix.

Below is the code i have but seems to result to what i want.

SELECT CONCAT('SELECT * FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'm_appuser'
AND TABLE_SCHEMA LIKE 'mifostenant%'

Result of the above is as below;

Your kind help is highly appreciated.

Answer 1

I think you actually want to use GROUP_CONCAT as per this StackOverflow answer.

This will allow you to prepare all rows into one statement that you can execute.