Dynamic sql built into select query

Question

I have a Users table,

  ╔════╦══════════════╗
 ║ Id ║ Name ║ Db ║
 ╠════╬══════════════╣
 ║ 1 ║ Peter ║ DB1 ║
 ║ 2 ║ John ║ DB16 ║
 ║ 3 ║ Alex ║ DB23 ║
 ╚════╩══════════════╝

and many databases with the same structure (the same tables, the same procedures, etc.), so each database has a table called Project , and this is the structure of the Project table,

  ╔════╦════════╦═════════════╗
 ║ Id ║ Request ║ Information ║
 ╠════╬════════╬═════════════╣
 ║ 1 ║ 126 ║ XB1 ║
 ║ 2 ║ 126 ║ D6 ║
 ║ 3 ║ 202 ║ BM-23 ║
 ╚════╩════════╩═════════════╝

So, when I query the database:

 SELECT count(distinct([Request])) as nbrRequests FROM [SRV02].[DB1].[dbo].[Project] 

I get this result:

  ╔═════════════╗
 ║ NbrRequests ║
 ╠═════════════╣
 ║ 2 ║
 ╚═════════════╝

Now I want to "bind" / "join" ... the results from the Users table to this query, where the Db column in the Users table is the name of my database, so I can get this result:

  ╔════╦══════════════╦════════════╗
 ║ Id ║ Name ║ Db ║ NbrRequests ║
 ╠════╬══════════════╬════════════╣
 ║ 1 ║ Peter ║ DB1 ║ 2 ║
 ║ 2 ║ John ║ DB16 ║ 3 ║
 ║ 3 ║ Alex ║ DB23 ║ 6 ║
 ╚════╩══════════════╩════════════╝

I am trying to use dynamic SQL but no luck.

NB: Each user has only one database, and the database belongs to only one user, this is a one-to-one relationship

Answer 1

By combining these 2 answers fooobar.com/questions/1244424 / ... and fooobar.com/questions/1244424 / ... , I got this solution:

 DECLARE @Query NVARCHAR(MAX)= 'SELECT u.Id, u.Name, u.Db, dbCts.nbrRequests FROM [Users] u INNER JOIN ('; DECLARE @QueryLength INT= LEN(@Query); SELECT @Query = @Query +CASE WHEN LEN(@Query) > @QueryLength THEN ' UNION ' ELSE '' END +'SELECT '''+Db+''' as db, count(distinct(Request)) as nbrRequests FROM [SRV02].'+Db+'[Project]' FROM Users; SET @Query = @Query+') dbCts ON u.Db = dbCts.db'; EXECUTE (@Query); 

Answer 2

The way you can do this is that UNION counts each specific database table and gives it identification for the database, for example:

 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 

Remember to add the server and schema to the Users table, which I did not do, because there is no such information in your question.

Also, for this, your connected user must have privileges for all databases.

Answer 3

Dynamic SQL can be very complex.

This example creates a select query from the users table. The @Query variable is incremented for each row returned by the Users table. Each row returns a query that connects the local user table to the project table in the remote db. The results of each query are UNIONED together.

Example

 -- Wil holds our dynamic query. DECLARE @Query NVARCHAR(MAX) = ''; -- Builds our dynamic statement. SELECT @Query = @Query + CASE WHEN LEN(@Query) > 0 THEN ' UNION ALL ' ELSE '' END + 'SELECT u.Id, u.Name, u.Db, COUNT(DISTINCT p.Request) AS NbrRequest ' + 'FROM [SVR02].' + QUOTENAME(DB) + 'dbo.Project AS p INNER JOIN Users u ON u.Db= p.Db ' + 'GROUP BY u.Id, u.Name, u.Db' FROM Users ; -- Executes the dynamic statement. EXECUTE (@Query); 

This example uses QUOTENAME to avoid SQL injection.