CODEWITHSUNDEEP
sql-serverdatabase
CorribView
CorribView

Reputation: 741

Collate data from different Databases into same output window

I need to display data in different columns in the same output window from multiple different database sources. It would be ok to output this data to a file if necessary. For example say I have the following script that I need run on databases with identical schema:

SELECT TOP 3 item_id, COUNT(*) as itemcount_db1 
FROM DB1.dbo.table 
GROUP BY item_id ORDER BY itemcount_db1

SELECT TOP 3 item_id, COUNT(*) as itemcount_bd2 
FROM DB2.dbo.table 
GROUP BY item_id ORDER BY itemcount_bd2

So that the output would not be in two sequential and separate windows (as I hundreds of DBs and want to do a single copy and paste). I'm happy to create all of the individual scripts to get the data, just need to combine them somehow.

Upvotes: 1

Views: 43

Answers (1)

S3S
S3S

Reputation: 25112

For one, you can use sp_MSforeachdb or a potential better one by Aaron Bertrand so you don't have to copy and paste all the scripts. I'm not sure you'd want the results going horizontally here, but instead just create a column with the DB flag. Here is a way using UNION and a CTE (since you need the order by for TOP).

with db1 as(
    SELECT TOP 3 
        item_id, 
        COUNT(*) as itemcount
        ,'DB1'
    FROM 
        DB1.dbo.table 
    GROUP BY 
        item_id
    ORDER BY 
        itemcount_bd2)

db2 as(
    SELECT TOP 3 
        item_id, 
        COUNT(*) as itemcount
        ,'DB2'
    FROM 
        DB2.dbo.table 
    GROUP BY 
        item_id 
    ORDER BY 
        itemcount_bd2)


select * from db1
union all
select * from db2

Upvotes: 1

Related Questions