How to connect multiple databases in one datasource?

Hi

I have around 200 databases(each one holds different customers detail) in my sql server.All databases have tables with same name and data varies according to customer.I have added a datasource in grafana using the name of one of the 200 databases.How to add other databases to datasource?Should I specify all the names under database using “;”?

1 Like

200 databases? each with a different customer? That sounds really bizarre. I don’t want to sound rude, but I think you need to learn something about relational database design. Generally, you would expect to have a single database, with a customer table having a unique key column. This key value would then be added to the detail table (or tables) so SQL server can join up the customer to the details in the query.

Maybe - just maybe - you inherited the database design and can’t change it to something more conventional. If so, feel free to ignore my comments (or show it to whoever is responsible for the apparent mess you have to deal with). Either way, good luck. SQL is a wonderful thing to deal with - so long as you don’t do crazy things with the database/table layouts.

Cheers,
Michael Moore

1 Like

In MySQL, you can reference any database other than the one you chose for the datasource by specifying the databases name before the table. If I chose “User1” as my database in the datasource config, I can still directly reference the “User2” database, as long as my database user has permissions there.

# queries User1.transactions
SELECT * FROM transactions; 

# queries User2.transactions
SELECT * FROM User2.transactions;