Menu
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
821
Syntax for dynamic SQL is to make it string as below : 'SELECT statement'; To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below : EXEC sp_executesql N'SELECT statement';
Static or Embedded SQL are SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries.
A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors.
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.
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
