Solved

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

Posted on 2014-01-16
6
38 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 40

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 64
MS SQL Sever Import/export problem 7 52
Want an individual results display div 8 48
How to trim a value in SQL 2 28
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 describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

730 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