Solved

SQL Query - Query list of databases with identical structure and combine results

Posted on 2014-01-16
6
37 Views
Last Modified: 2016-03-28
I need to query multiple databases that contain the same structure and return the results into a single file.  I can do it with a lot of work by using UNION ALL between each select statement and fully defined db names in the FROM but given that only the DB name is changing between each query it seems like a script would be better in case there are any changes (e.g. adding another field).

Note: DB's are all on the same server

Can anyone provide advice on how to best accomplish this?

Thanks in advance.
0
Comment
Question by:-James-
  • 3
6 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39787105
Create a temp table in one of the databases. Then run an insert statement similar to this:
exec sp_msforeachdb "insert into TheDb..temptbl select * from ?..tbl"

Open in new window

to collect your data. If you want to add the actual db as a column, use '?' .
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39830548
There are a few free tools like:

http://www.apexsql.com/promo/apexsql-diff_free-version.aspx?gclid=CPnLk7zesLwCFU5cfgod2y4AKA

http://dbcomparer.com/

And Microsoft SQL own (which I would recommend):
"SQL Server Data Tools (SSDT) includes a Schema Compare utility that you can use to compare two database definitions."
http://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx
0
 

Author Comment

by:-James-
ID: 39833384
Qlemo - If I understand sp_msforeachdb properly it is going to cycle through every db on the server.  I'm only looking to query from a set of financial databases (17 out of 30 + db's) .  Is there a way to identify the db names and then use a loop to cycle through the db's and union the results?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39834222
You'll need to code that as an IF in the SQL executed, like in
exec sp_msforeachdb "if '?' like 'Finance%' insert into TheDb..temptbl select * from ?..tbl"

Open in new window

sp_msforeachtable support a where condition, but sp_msforeachdb does not, sadly.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41525187
I've requested that this question be closed as follows:

Accepted answer: 500 points for Qlemo's comment #a39834222

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question