How to loop through several database in SQL server 2005?

Question

I have around 50 different databases and inside each of these database there is a table with the same name. Now, I have a written a query which extract selected fields from the table, but I need to manually select the database. How do I write a query so that I can pass database name and pull the data from that table? Here is my code:

;WITH Review as (
select  external_id as sponsorid, name as sponsorname, memberid, startdate, paidamt, code
from  test.dev.sraw c left join client.dbo.sponsors s on c.customerid = s.external_id
where   year(startdate) >2009   and startdate <> '0001-01-01'   and startdate <> '1000-01-01'),

 FinalDataCollection as (select sponsorid,  sponsorname, count(*) as NbrOfClaims,  count(distinct memberid) as NbrOfMembers,  month(startdate) as mnth,  year(startdate) as yr,  sum(cast(paidamt as money)) as dollars, case when  code > '0' then 'RX' else 'med' end as category, case when  count(distinct memberid)> 0 then sum(cast(paidamt as money))/count(distinct memberid) else 0 end as costpm
 from Review
 group by sponsorid,  sponsorname,year(startdate), month(startdate),case when  code > '0' then 'RX' else 'med' end)

 select * from FinalDataCollection

UPDATE #1 How do I replace ''SELECT top 5 * FROM '' + @db + ''.dbo.RAW'' of the following query with the above SQL query that start with ;WITH CTE

DECLARE @dbname NVARCHAR(200)
DECLARE @SQLString NVARCHAR (MAX)
SET @SQLString = 
'DECLARE @db NVARCHAR(255)
DECLARE DB_CURSOR  CURSOR LOCAL FAST_FORWARD FOR
        SELECT db.name from sys.databases db WHERE db.name  IN 
        (''A'',''B'',''C'' ) ORDER BY db.name
    OPEN DB_CURSOR
    FETCH NEXT FROM DB_CURSOR INTO @db
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(''SELECT top 5 * FROM '' + @db + ''.dbo.RAW'')
        FETCH NEXT FROM DB_CURSOR INTO @db
    END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR'
EXEC sp_executesql @SQLString

Thank you

read up on dynamic Sql and teh problems inhernet in using it. sommarskog.se/dynamic_sql.html — HLGEM
– HLGEM, Commented May 19, 2014 at 20:16

Bogdan Sahlean · Accepted Answer · 2014-05-22 14:53:11Z

1

You could create a stored procedure having one parameter @DbName thus:

CREATE PROCEDURE dbo.GetDataFromMyTable (
@DbName SYSNAME -- or NVARCHAR(128)
)
AS
BEGIN
IF DB_ID(@DbName) IS NOT NULL AND @DbName IN (N'MyDB1', N'MyDB2', N'MyDB3', ...)
BEGIN 
    DECLARE @SqlStatement NVARCHAR(MAX);
    SET @SqlStatement = N'USE ' + QUOTENAME(@DbName) + N'; SELECT t.Col1, t.Col2 FROM dbo.MyTable';
    EXEC sp_executesql @SqlStatement;
END
ELSE
    RAISERROR('Wrong database.', 16, 1);
END

END;

Notes:

DB_ID(@DbName) IS NOT NULL -- It checks if @DbName exists

@DbName IN (N'MyDB1', N'MyDB2', ..., N'MyDBn') -- It checks if @DbName is on the white list with allowed database (from this point of view, first check is, somehow, redundant).

Why I've used QUOTENAME(@DbName) ? See section Wrapping Parameters with QUOTENAME() and REPLACE(): if the content of @variable is a securable (ex. a database / table) then the recommended wrapper is QUOTENAME(@variable).

You could replace @DbName IN (N'MyDB1', N'MyDB2', ..., N'MyDBn') with exists thus:

EXISTS (
    SELECT * 
    FROM ( 
        SELECT N'MyDB1' UNION ALL 
        SELECT N'MyDB2' UNION ALL 
        ...
        SELECT N'MyDBn'
    ) dbs(DbName)
    WHERE dbs.DbName = @DbName
)

Update #1:

You have to replace

SELECT t.Col1, t.Col2 FROM dbo.MyTable

with

WITH Review as (
select  external_id as sponsorid, name as sponsorname, memberid, startdate, paidamt, code
from  test.dev.sraw c left join client.dbo.sponsors s on c.customerid = s.external_id
where   year(startdate) >2009   and startdate <> ''0001-01-01''   and startdate <> ''1000-01-01''),

 FinalDataCollection as (select sponsorid,  sponsorname, count(*) as NbrOfClaims,  count(distinct memberid) as NbrOfMembers,  month(startdate) as mnth,  year(startdate) as yr,  sum(cast(paidamt as money)) as dollars, case when  code > ''0'' then ''RX'' else ''med'' end as category, case when  count(distinct memberid)> 0 then sum(cast(paidamt as money))/count(distinct memberid) else 0 end as costpm
 from Review
 group by sponsorid,  sponsorname,year(startdate), month(startdate),case when  code > ''0'' then ''RX'' else ''med'' end)

 select * from FinalDataCollection

edited May 22, 2014 at 14:53

answered May 19, 2014 at 20:27

Bogdan Sahlean

1

Sign up to request clarification or add additional context in comments.

10 Comments

poshan Over a year ago

Hi Bogdan, my select statement involves CTE. How do I handle that? Thank you

Bogdan Sahlean Over a year ago

You have to change SET ... + N'; SELECT t.Col1, t.Col2 FROM dbo.MyTable'; with your query SET ... + N'; WITH CTE1(...) SELECT t.Col1, t.Col2 FROM dbo.CTE1';

poshan Over a year ago

Hi Bogdan, for some reason I couldn't get it work. I have modified my original question with the code that I am trying to make dynamic. Could you please review it and suggest me how I can make it workable. Thank you

poshan Over a year ago

Thank you Bogdan. This is working now. I really appreciate it.

RBarryYoung Over a year ago

I just create a common database for this kind of thing. Works for me.

|

Collectives™ on Stack Overflow

How to loop through several database in SQL server 2005?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related