run query through 50+ 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 2000 Forums

SQL Server Development (2000)

run query through 50+ databases

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-02 : 21:44:54

We have about 55 or so databases that I use and each always starts with the 3 letters XMP. example: XMP001, XMP002, XMP010

how can I run this query through all 55 databases?


Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
	join FundCustomFieldValues b on  d.fieldId = b.FieldId
	join FundInfo c on b.FundId = c.FundId
Where  d.FieldName = 'Fund Detail'
Order by b.FundId ASC

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-02 : 21:50:45

Using

sp_Msforeachdb Store Proc.

Do they have these tables in all databases?

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-02 : 22:02:00

the databases look exactly the same.

how do i use sp_Msforeachdb

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-02 : 22:08:43

but, there are other databases in there. I would just want to the ones that start with XMP though. name like 'XMP%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 02:35:16

You can try like this:-

Exec sp_MSforeachdb @command1="if '?' like 'XMP%' 
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
	join FundCustomFieldValues b on  d.fieldId = b.FieldId
	join FundInfo c on b.FundId = c.FundId
Where  d.FieldName = 'Fund Detail'
Order by b.FundId ASC"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-05-03 : 02:52:20

using SQLCMD and a batch file:


for /F "tokens=1 delims=," %%i in (all_my_databases.txt) do (
	SQLCMD -d %%i -i myscript.sql -o %%i_results.txt -SMYSERVER -E
)

elsasoft.org

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-03 : 06:19:49

quote:

Originally posted by visakh16

You can try like this:-

Exec sp_MSforeachdb @command1="if '?' like 'XMP%' 
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
	join FundCustomFieldValues b on  d.fieldId = b.FieldId
	join FundInfo c on b.FundId = c.FundId
Where  d.FieldName = 'Fund Detail'
Order by b.FundId ASC"

Ok, one more question.. since I can't really test this out until Monday @ work. do I need to add something at the end of my query so its all one output? or will the above automatically have the results this way? ie; will that stored procedure auto union all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 06:28:07

quote:
Originally posted by QuietRiot

quote:
Originally posted by visakh16

You can try like this:-
Exec sp_MSforeachdb @command1="if '?' like 'XMP%'
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
join FundCustomFieldValues b on d.fieldId = b.FieldId
join FundInfo c on b.FundId = c.FundId
Where d.FieldName = 'Fund Detail'
Order by b.FundId ASC"

Ok, one more question.. since I can't really test this out until Monday @ work. do I need to add something at the end of my query so its all one output? or will the above automatically have the results this way? ie; will that stored procedure auto union all

Nope. no need of any union all. It will automatically run the query for each db and gives the result as a single o/p.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-03 : 14:53:40

Just curious jezemine, your batch file/SQL CMD looks interesting. I have never used it. What it actually does?

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-05 : 07:33:06

I'm getting an error message now..

Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'if '?' like 'XMP%'
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinit' is too long. Maximum length is 128.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-05 : 07:57:38

damn, doesn't seem to be working the way i want it too.. I tried:


Exec sp_MSforeachdb @command1="if '?' like 'XMP%'
	Select * from '?'..FundInfo"

because I was getting
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'FundInfo' when i just had FundInfo there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 07:59:44

quote:
Originally posted by QuietRiot

I'm getting an error message now..

Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'if '?' like 'XMP%'
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinit' is too long. Maximum length is 128.

Try like this:-

Exec sp_MSforeachdb @command1='if ''?'' like ''XMP%'' 
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
	join FundCustomFieldValues b on  d.fieldId = b.FieldId
	join FundInfo c on b.FundId = c.FundId
Where  d.FieldName = ''Fund Detail''
Order by b.FundId ASC'

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-05 : 08:04:26

quote:
Originally posted by visakh16

quote:
Originally posted by QuietRiot

I'm getting an error message now..

Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'if '?' like 'XMP%'
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinit' is too long. Maximum length is 128.

Try like this:-

Exec sp_MSforeachdb @command1='if ''?'' like ''XMP%''
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
join FundCustomFieldValues b on d.fieldId = b.FieldId
join FundInfo c on b.FundId = c.FundId
Where d.FieldName = ''Fund Detail''
Order by b.FundId ASC'

i get Invalid object name 'FundGroupInfo' etc but for all my query columns..

do i need to tell it what database? like '?'..FundGroupInfo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 08:06:31

quote:
Originally posted by QuietRiot

quote:
Originally posted by visakh16

quote:
Originally posted by QuietRiot

I'm getting an error message now..

Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'if '?' like 'XMP%'
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinit' is too long. Maximum length is 128.

Try like this:-

Exec sp_MSforeachdb @command1='if ''?'' like ''XMP%''
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From FundGroupInfo a, FundCustomFieldDefinitions d
join FundCustomFieldValues b on d.fieldId = b.FieldId
join FundInfo c on b.FundId = c.FundId
Where d.FieldName = ''Fund Detail''
Order by b.FundId ASC'

i get Invalid object name 'FundGroupInfo' etc but for all my query columns..

do i need to tell it what database? like '?'..FundGroupInfo

Dont you have FundGroupInfo table in all dbs?

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-05 : 08:08:52

I do. its in all my XMP databases. i get the error message for all my objects
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundGroupInfo'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundCustomFieldDefinitions'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundCustomFieldValues'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundInfo'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundGroupInfo'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundCustomFieldDefinitions'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundCustomFieldValues'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FundInfo'.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-05-05 : 08:23:37

Ok, I think I got it now..


Exec sp_MSforeachdb @command1='if ''?'' like ''XMP%'' 
Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValue
From ?..FundGroupInfo a, ?..FundCustomFieldDefinitions d
	join ?..FundCustomFieldValues b on  d.fieldId = b.FieldId
	join ?..FundInfo c on b.FundId = c.FundId
Where  d.FieldName = ''Fund Detail''
Order by b.FundId ASC'

thank you for all your help.. hopefully this is good enough :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources