Run query across multiple databases to find out how many times a username is duplicated

Vote count:

0

I have a single tenancy web app (every single tenant has their own database). I am trying to move it to a multi tenancy architecture, but before I do this I am trying to identify conflicts.

Currently I am trying to figure out which usernames might be duplicated between the many different tenants.

My solution so far is to write a query that does something like this:

SELECT `user`, count(*) FROM (     
    (SELECT * FROM `100001`.`user`) UNION
    (SELECT * FROM `100002`. `user`) UNION
    (SELECT * FROM `100003`. `user`) UNION
    (SELECT * FROM `100004`. `user`) UNION
    (SELECT * FROM `100005`. `user`) UNION
    ...
) as `all_users`
GROUP BY `user`

This works great, but does not scale well when you realize there are already over 100 databases.

I start thinking about using CONCAT to help me generate the query but got stuck at this:

SELECT CONCAT('(SELECT * FROM `', SCHEMA_NAME, '`.`user`)')
FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` REGEXP '[0-9]{6}';

Any thoughts on where to go from here?

asked 3 mins ago

Roeland

798

Run query across multiple databases to find out how many times a username is duplicated