Solved

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

Posted on 2014-01-16
6
27 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 68

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 68

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 68

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access recordset not updateable 8 38
SQL Query to display duplicates ? 6 27
search for a string in all tables 4 15
Test a query 23 18
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now