Sql-server – Selecting rows from same name table from multiple databases in single result set

sql serversql-server-2008sql-server-2008-r2table

For some reason, I have a table Table_1 in n databases (same name and schema structure) instead of one single databases. The columns are like this:

| Column_1 | Column_2 |

I can select data from those n databases by running this query on each of them:

use Database_n
select * from Table_1

Problem: This will require me to run this query n times. Which will return n result sets as shown in the below image:

n number of result sets

Question: Is there a way to get the data from all those databases in one single result set like this:

one single result set

Best Answer

Sure :

    select * from database_n.dbo.Table_1
    UNION ALL
    select * from database_n+1.dbo.Table_1
    UNION ALL
    select * from database_n+2.dbo.Table_1
    -- etc.
Related Question