How do I change databases while using Cursors or something similar?

Question

I have say a couple of statements that gets values from a database e.g:

declare @intValue = 0;
use [databaseA]
select @intValue = @intValue + columnA from tableA where id = 5;

The above statement has to be made multi database so I would like to do something like:

declare @intValue = 0;
use [databaseA]
select @intValue = @intValue + columnA from tableA where id = 5;

use [databaseB]
select @intValue = @intValue + columnA from tableA where id = 5;

Now is there a way to simplify the above query without copying and pasting several times if i have multiple databases WITHOUT using dynamic SQL?

I'm hoping for something with a cursor or something that might work out?

It might be something like

for each database in DatabaseList
use [database]
select **** statements;
end for

I'm not sure if it's possible without using dynamic SQL.

AFAIK, You can't use a variable in the `use` statement. [Read this](http://stackoverflow.com/questions/3788566/how-can-i-do-something-like-use-databasename) — Zohar Peled, Jul 29 '15 at 05:39
better to use 'database.schema.table', instead of using 'use database' — koushik veldanda, Jul 29 '15 at 05:54
@ZoharPeled - That was a gem. One good answer which might help the OP - http://stackoverflow.com/a/3788957/2993606 — SouravA, Jul 29 '15 at 06:10

score 1 · Answer 1 · answered Jul 29 '15 at 06:04

1

You can't do that. But there is another way of looping across databases by using the sp_msforeachdb stored proc.

For this to work, store the initial value into a temp table and then just update this temp table if you find matches

create table #value(intvalue int)
insert into #value
select '10'

exec sp_MSforeachdb 'use [?]; 
                     update #value set intvalue = intvalue + 
                     ISNULL((select sum(columnA) from tableA 
                     where id = 5), 0)'

The SUM function will ensure that the inner query returns only one row ever. ISNULL will convert any NULL values to 0.

sp_msforeachdb internally uses a cursor to loop across the databases but I recommend this method because of the simplicity of it.

answered Jul 29 '15 at 06:04

SouravA

4,967
2
20
45

Note that `sp_msforeachdb` is an [undocumented feature](http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx). Aaron Bertrand provides [a more reliable and more flexibe sp_msforeachdb](http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx) – Felix Pamittan Jul 29 '15 at 06:11
@FelixPamittan - I agree. But I see people using this all the time :) Thank you for sharing. – SouravA Jul 29 '15 at 06:12
2

That doesn't make it safe. It's probably the reason why experts such as Bertrand wrote a reliable alternative. – Felix Pamittan Jul 29 '15 at 06:13

score 0 · Answer 2 · answered Jul 29 '15 at 07:59

I have used undocumented sp_MsForEachDB stored procedure for such tasks Another very similar stored procedure is the sp_MSForEachTable for running a script on every table in the database

Here is a script where I had to minimize sql command for 128 character limit

EXEC sp_Msforeachdb "
use [?];
declare @i int=0;
if OBJECT_ID('tableA') is not null
select @i=@i+columnA from tableA where id=5;select @i,'[?]'
"

How do I change databases while using Cursors or something similar?

2 Answers2