Run a query against 95% of my databases

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

SQL Server 2005 Forums

Transact-SQL (2005)

Run a query against 95% of my databases

Author

Topic

rternier
Starting Member

44 Posts

Posted - 2008-12-04 : 13:51:18

Our Database server has over 500 databases. Roughly 450 of these I need to run a query against to see the XML contents in one of the tables.

The problem is, I can't distinguish the differences between the databases using any naming convention because some tables might use a seperate part of our framework so they don't need the ProcessConfigurations table i'm querying.

I'm doing something like http://weblogs.sqlteam.com/tarad/archive/2007/11/28/60417.aspx

but i'm lost in how to fully accomplish it.

I need to go through each database, if it has tblProcessConfigurations, I need to query the rows in that table in that database.

Any help would rock - and all DB's are on the same server.

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 13:58:09

Put your query inside sp_Msforeachdb and exclude system databases.

rternier
Starting Member

44 Posts

Posted - 2008-12-04 : 14:04:09

I tried this:


declare @cmd1 varchar(500)
set @cmd1 =
    'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblProcessConfigurations]'') AND type in (N''U''))
BEGIN
PRINT ''hi''
SELECT * FROM tblProcessConfigurations
END'

exec sp_MSforeachdb  @command1=@cmd

but I get no results, nothing in text results, or grid results. what am I missing?

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 14:18:18

Use like this:

Exec sp_MSforeachdb @command1= 'USE ? IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
begin
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''[dbo].[tblProcessConfigurations]'') AND type in (''U''))
BEGIN
PRINT ''hi''
SELECT * FROM dbo.tblProcessConfigurations
END
end'

rternier
Starting Member

44 Posts

Posted - 2008-12-04 : 14:30:26

That works! Thanks a bunch sodeep!

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 14:31:13

Cool

.Glad it worked out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources