Run One Query on all Databases on Server

[yeagly21]

I am tyring to figure out how I can run the same query against all databases on the server at one time. I have over 100 databases on this server and I figure there has to be an easy way to run this simple query on all of the databases.

For simplicity we will say the query is:

SELECT Job_ID
FROM Jobs
WHERE PostDate >= '6/8/2006'

If anyone can help I would appreciate it.

 

[SQLDenis]

try this

EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
SELECT  ''?'',Job_ID
FROM Jobs
WHERE PostDate >= ''6/8/2006'' '

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

small modification

EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
SELECT  ''?'',Job_ID
FROM [b][?]..[/b]Jobs
WHERE PostDate >= ''6/8/2006'' '

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

exec dbo.sp_MSforeachdb 'IF NOT OBJECT_ID(''?.dbo.Jobs'') IS NULL SELECT Job_ID
FROM ?.dbo.Jobs
WHERE PostDate >= ''6/8/2006'' else print ''?'''

(not tested)


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLDenis]

bborissov

?.dbo.Jobs
has to be

[?].dbo.Jobs

or the query will break if the database name has a space in it
just to let you know

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Oops, yes of course. Thanks.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLDenis]

And yes I should have added the check for the table name like you did for databses that don't have those tables ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

I didn't add it first, but it bombs when i tried this query on my Sql Serever

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[yeagly21]

Thanks guys that worked great