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.

WHERE TABLE_NAME = 'm_appuser'
AND TABLE_SCHEMA LIKE 'mifostenant%'

Result of the above is as below; Result of the above

Your kind help is highly appreciated.

  • Welcome to DBA.SE. Could it be that your title would better written as: How to select all data from all tables for a given schema/prefix? Your current statement creates a generated list of SELECT statements that could be used to select the data. You would like to automate this second step. Do I understand your question correctly?
    – John K. N.
    Jan 6, 2021 at 6:44
  • @John K. N. I think that's what I'm looking for. Your kind help on the same will be highly appreciated. Apr 12, 2021 at 15:46

1 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.

  • Thanks for your kind contribution but it seemed not to sort out my problem. May be you can guide me further on how to apply the the GROUP_CONCAT in this same scenario. Thannks Jan 6, 2021 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.