-1

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; Result of the above

Your kind help is highly appreciated.

2
  • 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.
    Commented 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. Commented Apr 12, 2021 at 15:46

2 Answers 2

0

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.

1
  • 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 Commented Jan 6, 2021 at 14:34
0

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

You can use the following script (based upon your query):

myscript.sql:

TEE output.txt
SELECT 'STARTING the script...' AS '';
SELECT 
  CONCAT('SELECT "', TABLE_SCHEMA, '.', TABLE_NAME, '" AS ', '""', '; ', 'SELECT * FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM 
  INFORMATION_SCHEMA.TABLES 
WHERE 
  TABLE_NAME = 'm_appuser' AND 
  TABLE_SCHEMA LIKE 'mifostenant%'  
INTO OUTFILE 'query.txt';

SOURCE query.txt;
SELECT 'DONE.' AS '';
NOTEE

Run it from mysql client:

SOURCE myscript.sql;

This will create a result text file "output.txt", similar to this:

+------------------------+
|                        |
+------------------------+
| STARTING the script... |
+------------------------+
1 row in set (0.00 sec)

Query OK, 2 rows affected (0.00 sec)

+------------------------+
|                        |
+------------------------+
| mifostenant1.m_appuser |
+------------------------+
1 row in set (0.00 sec)

+-----+---------+
| c1  | c2      |
+-----+---------+
|   5 | five    |
|   4 | four    |
+-----+---------+
2 rows in set (0.00 sec)

+------------------------+
|                        |
+------------------------+
| mifostenant2.m_appuser |
+------------------------+
1 row in set (0.00 sec)

+-----+---------+
| c1  | c2      |
+-----+---------+
|   1 | NULL    |
|   5 | five    |
|   4 | four    |
+-----+---------+
3 rows in set (0.00 sec)

+----------------------+
|                      |
+----------------------+
| DONE.                |
+----------------------+
1 row in set (0.00 sec)

Your Answer

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

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