CODEWITHSUNDEEP
mysqlsql
Gurdt
Gurdt

Reputation: 181

SQL Query to SELECT from multiple Schema's

I have an database with alot of users (schema's) for example:

All of those got the same tables, for example:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `content` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `viewed` int(11) NOT NULL,
  `forId` int(11) NOT NULL,
  `fromId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `messages` (`id`, `content`, `date`, `viewed`, `forId`, `fromId`) VALUES
(1, 'Hello World', '2020-06-04 14:49:17', 1, 2106, 1842),
(2, 'Hi there', '2020-06-04 14:49:39', 1, 2106, 1842),
(3, 'test 1', '2022-01-03 11:40:43', 1, 3006, 3006),
(4, 'Test 2', '2022-01-20 12:01:52', 1, 1842, 1842);

What I want is a query for example:

USE user_1, user_2, user_3;

SELECT * FROM `messages` WHERE `content` LIKE `%Hi%`;

I don't know if this is possible as a SQL Query, an other option is to write a small PHP code with a for each loop but than I want a command so I get an list of all users: user_1 till user_303

The users are not from 1 till 303 there are some users deleted, to it can be that user_200 doesn't exist any more.

Hope someone here can help me out

Upvotes: 0

Views: 3957

Answers (2)

ev-i
ev-i

Reputation: 1

In Oracle you can concatenate only 2 arguments, so you have to use nesting to concatenate more than two arguments. Also you should use ALL_TABLES instead of information_schema

SELECT
    concat('SELECT MESSAGE FROM ', concat(OWNER, concat('.', concat(TABLE_NAME, ' UNION ALL '))))
FROM
    ALL_TABLES
WHERE
    OWNER LIKE 'user_%';

Don't forget to delete the last UNION ALL from result.

Upvotes: 0

pringi
pringi

Reputation: 4682

You can use the following to write the query you want.

USE information_schema;

SELECT concat("SELECT * FROM ", table_schema,".",table_name, " UNION ALL ") 
FROM tables WHERE table_name = 'messages';

You will obtain something like this;

SELECT * FROM base.messages UNION ALL
SELECT * FROM c.messages UNION ALL

You can then run this query to obtain what you want.

Upvotes: 2

Related Questions