I want to combine tables from different schemas using UNION\_ALL. The tables have the same schema, like in this toy example:

```
class1.names
+----------+
| id | name|
+----------+
| 1  | jon |
| 2  | ann |
| 3  | rob |

class2.names
+----------+
| id | name|
+----------+
| 1  | rav |
| 2  | meg |
| 3  | mat |

```

I can hardcode the list of classes into an array, or more preferably, obtain them using a query like so:

```
SELECT DISTINCT(TABLE_SCHEMA)
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

```

I want to combine the tables like so:

```
SELECT *, 'class1' as class FROM class1.names
UNION_ALL
SELECT *, 'class2' as class FROM class2.names
UNION_ALL
etc.

```

But there will be a lot more going on in the schema-level queries than `SELECT *, 'class1'...` so I want to do this using a loop or some other systematic method.

I'm looking at dynamic sql or using GROUP\_CONCAT with 'UNION\_ALL' as a separator, but I'm having trouble making progress.

Addendum: I know this is poor schema design but I can't do anything about that right now.