Run a query against multiple databases

17 Aug 2006 9:03 PM

Skeptical

Hello,

I had asked this question before but I guess my description was not
very clear.

I am able to select all databases from master..sysdatabases, but I am
not sure how to run queries against each so I have something like:

Database Result
database1 1
database2 2

In one table.

I can use a cursor and run queries against each but that results in x
number of tables x being the number of databases in the system. I
somehow need to collect all the results into one table so I can sort
etc.

Any ideas?

17 Aug 2006 9:29 PM

Rick Sawtell

Show quote

"Skeptical" <gun***@gmail.com> wrote in message
news:1155848612.415652.169840@i3g2000cwc.googlegroups.com...
> Hello,
>
> I had asked this question before but I guess my description was not
> very clear.
>
> I am able to select all databases from master..sysdatabases, but I am
> not sure how to run queries against each so I have something like:
>
> Database Result
> database1 1
> database2 2
>
> In one table.
>
> I can use a cursor and run queries against each but that results in x
> number of tables x being the number of databases in the system. I
> somehow need to collect all the results into one table so I can sort
> etc.
>
> Any ideas?
>

use SomeDatabase
GO

CREATE TABLE Foo (
    dbname    sysname     NOT NULL,
    value1      varchar(100),
    ..
    ..
)
GO

DECLARE @dbname    sysname

DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT [name] FROM master.dbo.sysdatabases
-- WHERE clause to remove db's you don't want. (tempdb, msdb, model etc)

OPEN cur
FETCH NEXT
FROM cur
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE ('INSERT SomeDatabase.dbo.Foo SELECT ''' + @dbname + ''',
value1, value2 ... FROM [' + @dbname + '].dbo.TableName WHERE....')

    FETCH NEXT
    FROM cur
    INTO @dbname

END

CLOSE cur
DEALLOCATE cur

SELECT * FROM Foo

DROP TABLE Foo

Rick Sawtell
MCT, MCSD, MCDBA

SELECT * FROM

17 Aug 2006 9:51 PM

Skeptical

I was so close : )

Thanks much is there any reason why you used a regular table versus a
temp table?

Rick Sawtell wrote:
Show quote

> "Skeptical" <gun***@gmail.com> wrote in message
> news:1155848612.415652.169840@i3g2000cwc.googlegroups.com...
> > Hello,
> >
> > I had asked this question before but I guess my description was not
> > very clear.
> >
> > I am able to select all databases from master..sysdatabases, but I am
> > not sure how to run queries against each so I have something like:
> >
> > Database Result
> > database1 1
> > database2 2
> >
> > In one table.
> >
> > I can use a cursor and run queries against each but that results in x
> > number of tables x being the number of databases in the system. I
> > somehow need to collect all the results into one table so I can sort
> > etc.
> >
> > Any ideas?
> >
>
> use SomeDatabase
> GO
>
> CREATE TABLE Foo (
>     dbname    sysname     NOT NULL,
>     value1      varchar(100),
>     ..
>     ..
> )
> GO
>
> DECLARE @dbname    sysname
>
> DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR
> SELECT [name] FROM master.dbo.sysdatabases
> -- WHERE clause to remove db's you don't want. (tempdb, msdb, model etc)
>
> OPEN cur
> FETCH NEXT
> FROM cur
> INTO @dbname
> WHILE @@FETCH_STATUS = 0
> BEGIN
>     EXECUTE ('INSERT SomeDatabase.dbo.Foo SELECT ''' + @dbname + ''',
> value1, value2 ... FROM [' + @dbname + '].dbo.TableName WHERE....')
>
>     FETCH NEXT
>     FROM cur
>     INTO @dbname
>
> END
>
> CLOSE cur
> DEALLOCATE cur
>
> SELECT * FROM Foo
>
> DROP TABLE Foo
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> SELECT * FROM

21 Aug 2006 2:53 PM

Rick Sawtell

Temp table will disappear when the script ends.

I didn't know if you wanted to keep it for reporting purposes. I'm not
sure that a local temp table would be visible to the EXECUTE statements as
they open their own chunk of memory and processing, so you would have to go
with a global temp table.

Rick