Running query on all databases within an SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Running query on all databases within an SQL

Author

Topic

Scanner001
Starting Member

4 Posts

Posted - 2007-10-03 : 08:43:08

Hi,

I'm running SQL 2000 SP4.

On this server I have a few databases. I need to run the below query against each database. Since the server has approx 50 databases this will take time.

Is there any way for me to modify this so as it will automatically
run against each database one at a time and then create a single output file with all the results.

I do not want it to run against all of the databases at the same time as it will kill my server so it must run and gather results on each database at a time.

QUERY to be run

SELECT RelativeFileName

FROM Collection, Saveset

WHERE Collection.CollectionIdentity = Saveset.CollectionIdentity

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 08:46:14

You could use sp_foreachdb, but its not documented

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 08:47:36

[code]sp_msforeachdb '
SELECT RelativeFileName
FROM Collection
INNER JOIN Saveset ON Saveset.CollectionIdentity = Collection.CollectionIdentity
'[/code]

E 12°55'05.25"
N 56°04'39.16"

Scanner001
Starting Member

4 Posts

Posted - 2007-10-03 : 08:50:24

Will this run it on each database one at a time and only move on to the next database once the first one is complete?

quote:
Originally posted by Kristen

You could use sp_foreachdb, but its not documented

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 08:51:07

sp_msforeachdb '
SELECT RelativeFileName
FROM ?.dbo.Collection
INNER JOIN ?.dbo.Saveset ON Saveset.CollectionIdentity = Collection.CollectionIdentity
'

Or maybe it is permitted to put a USE at the top ??

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 08:51:21

Did you try?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 08:52:05

Nope ... is that BAD? !!

Scanner001
Starting Member

4 Posts

Posted - 2007-10-03 : 08:52:30

I wanted to verify before I ran it because i'm worried i will take down my production database

quote:
Originally posted by Peso

Did you try?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 08:52:44

Anyways, its going to need a test to see if that table existing in that DB, isn't it? Thus avoiding running in Master, Tempdb, etc.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 08:58:43

quote:
Originally posted by Kristen

Nope ... is that BAD? !!

Oh, I meant OP...
Sorry for the confusion.

E 12°55'05.25"
N 56°04'39.16"

Scanner001
Starting Member

4 Posts

Posted - 2007-10-03 : 08:59:12

So if i just copy this and use as is it will give me my results ?

quote:
Originally posted by Kristen

sp_msforeachdb '
SELECT RelativeFileName
FROM ?.dbo.Collection
INNER JOIN ?.dbo.Saveset ON Saveset.CollectionIdentity = Collection.CollectionIdentity
'

Or maybe it is permitted to put a USE at the top ??

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 09:03:31

[code]sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
SELECT ''?'' AS DbName, RelativeFileName
FROM ?..Collection
INNER JOIN ?..Saveset ON Saveset.CollectionIdentity = Collection.CollectionIdentity
'[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources