Query Across DBs

Question

I'm trying to run a query across multiple databases. Each database is a different customer but I'm querying the same tables across all of them. I want to loop through all the databases (about 100) and put all the results in a table. I've tried a few different ways but I can't quite seem to get it working and it seems like it might be a syntax thing. Below is my code:

IF OBJECT_ID('KW.dbo.Result') IS NOT NULL DROP TABLE KW.dbo.Result;

SELECT name as DBName, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY name) AS RNo 
INTO #DBName
FROM sys.databases
WHERE name LIKE 'Customer%'
ORDER BY name;

SELECT * FROM #DBName

DECLARE @sql varchar;

SET @sql = N'SELECT bh.ID,
b.LINE AS Line#,
c.State,
bh.ZIP,
REPLACE(p.Phone, '-', '') AS Phone,
cc.COMPANY_NAME,
b.Amount
INTO KW.dbo.Result
FROM dbo.Customer c
     LEFT JOIN dbo.Change ch ON ch.CIDNo = c.CIDNo
     LEFT JOIN dbo.Provider p ON p.PIDNo = ch.PIDNo
     LEFT JOIN dbo.BossHead bh ON bh.CHIDNo = ch.CHIDNo
     LEFT JOIN dbo.Bills b ON b.BIDNo = bh.BIDNo
     LEFT JOIN dbo.PartnerTerms pt ON pt.BIdNO = b.BIdNo
     LEFT JOIN dbo.CompanyCode cc ON cc.CompanyCode = pt.CompanyCode
WHERE REPLACE(p.Phone, '-', '') IN
(
    SELECT *
    FROM KW.dbo.PhoneNumbers
)
      AND bh.CreateDate BETWEEN "09-01-2016" AND "10-01-2017"
      AND pt.CompanyCode IS NOT NULL
ORDER BY 1';

DECLARE @DB varchar;

DECLARE @i int;

BEGIN TRANSACTION;

SET @i = 1;

    WHILE @i <= (SELECT MAX(RNo) FROM #DBName)

        BEGIN

           SET @DB = (SELECT DBName FROM #DBName WHERE RNo = @i)

           USE @DB;

           EXECUTE sp_executesql @sql

           SET @i = @i + 1

        END

COMMIT TRANSACTION;

GO

Error Message: "Msg 102, Level 15, State 1, Line 55 Incorrect syntax near '@DB'."

Any help will be greatly appreciated, thank you!

Please edit your post and supply the error message or explain what the issue is. I just don't understand why people can't seem to do this. Looking at your code I don't see an insert in it anywhere. Is that the problem? — Nick.Mc, Dec 07 '17 at 23:48
For this particular attempt it was "Msg 102, Level 15, State 1, Line 55 Incorrect syntax near '@DB'." Sorry for not including it, still new to this. — , Dec 07 '17 at 23:56
You need to escape the single quotes inside the string. https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server — pabrams, Dec 07 '17 at 23:58
Sounds like the structure of having a database per customer is improper. — Nerds of Technology, Dec 07 '17 at 23:58
Customers would normally be represented as rows in a table; creating an identical database for each customer is usually a phenomenal waste, but maybe there's a good reason. — pabrams, Dec 08 '17 at 00:24
@pabrams I'm just an analytics intern so it's above my pay-grade but I figure the thinking behind using separate DBs is for keeping customer data secure and private. But I see where you're coming from, it would be a lot easier and less resource intensive your way. — , Dec 08 '17 at 00:38
[`Use`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql) takes an optional database name as an argument. Not a variable, expression, ... . Ergo, `USE @DB;` won't work. — HABO, Dec 08 '17 at 01:00

score 0 · Answer 1 · answered Dec 08 '17 at 01:05

You can't use a variable to switch tables. What you can do is create a dynamic query that uses the fully qualified database and table name to go across all of the databases at the same time.

Create a query that creates your query:

Use master

Select top(1) case when Row_Number() over (order by d.name) = 1 then '' else 
'union ' end + 'select c.name from ' + quotename(d.name) +'.' +'.[dbo].[Customer]'
From sys.databases d
Order by d.name

Work with that until the query does what you want, then remove the top 1 and run the whole query against all of your databases.

Query Across DBs

1 Answers1