I have a table Users,
╔════╦═══════╦══════╗ ║ Id ║ Name ║ Db ║ ╠════╬═══════╬══════╣ ║ 1 ║ Peter ║ DB1 ║ ║ 2 ║ John ║ DB16 ║ ║ 3 ║ Alex ║ DB23 ║ ╚════╩═══════╩══════╝
and many databases that have the same structure (Same tables, same procedures, ...), so every database have a table named Project, and this is the structure of Project table,
╔════╦═════════╦═════════════╗ ║ Id ║ Request ║ Information ║ ╠════╬═════════╬═════════════╣ ║ 1 ║ 126 ║ XB1 ║ ║ 2 ║ 126 ║ D6 ║ ║ 3 ║ 202 ║ BM-23 ║ ╚════╩═════════╩═════════════╝
So, when I query a database :
SELECT count(distinct([Request])) as nbrRequests
FROM [SRV02].[DB1].[dbo].[Project]
I get this result :
╔═════════════╗ ║ NbrRequests ║ ╠═════════════╣ ║ 2 ║ ╚═════════════╝
Now, what I want is to "link"/"join" ... results from the table Users to this query, where the column Db in Users table is the name of my database, so I can get a result like this :
╔════╦═══════╦══════╦═════════════╗ ║ Id ║ Name ║ Db ║ NbrRequests ║ ╠════╬═══════╬══════╬═════════════╣ ║ 1 ║ Peter ║ DB1 ║ 2 ║ ║ 2 ║ John ║ DB16 ║ 3 ║ ║ 3 ║ Alex ║ DB23 ║ 6 ║ ╚════╩═══════╩══════╩═════════════╝
I'm trying with dynamic SQL, but no luck.
NB : Every user has only one database, and a database belong to only one user, it's one-to-one relationship