dynamic sql embedded in select query

Question

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

Answer 1

The way you can do it is with a UNION counting every specific database table and giving it a identification for the database, like this:

SELECT u.Id, u.Name, u.Db, dbCts.nbrRequests
  FROM [Users] u INNER JOIN
      (SELECT 'DB1' as db, count(distinct([Request])) as nbrRequests
         FROM [SRV02].[DB1].[dbo].[Project]
       UNION 
       SELECT 'DB16', count(distinct([Request])) as nbrRequests
         FROM [SRV02].[DB16].[dbo].[Project]
       UNION  
       SELECT 'DB23', count(distinct([Request])) as nbrRequests
         FROM [SRV02].[DB23].[dbo].[Project]
      ) dbCts ON u.Db = dbCts.db

Don't forget to add the server and schema to the Users table I didn't because there is no such info on your question.

Also in order to do this, your connected user must have privileges on all databases.