How to give a SELECT in multiple databases at once?

Question

I have several databases on my server and I need to perform a query to return some information from a table that exists in all these other databases. How can I make a SELECT on several bases at the same time?

Answer 1

You’ll need to use sp_addlinkedserver, see the documentation. Once the server link is established, you can build your own query, using the name of your database with another server.

1° Example (Consultation with the INNER JOIN.):

SELECT *
FROM [MinhaBaseDeDadosNoDB1].[dbo].[MinhaTabela] tabela1
    INNER JOIN [DB2].[MinhaBaseDeDadosNoDB2].[dbo].[MinhaOutraTabela] tabela2
        ON tabela1.ID = tabela2.ID

2° Example (Consultation without the INNER JOIN.):

SELECT 
    *
FROM 
    MinhaTabelaLocal,
    [OutroNomeDeServidor].[OutroDB].[dbo].[OutraTabela]

Note that the owner is not the dbo, you must replace it with the schema you are using.

3° Example (Query on the local server.):

SELECT * 
FROM [master].[dbo].[spt_values]

Where master is my database, dbo is my schema and spt_values is the table I want to get information, there was no need to use the command user, just indicate the full path to the table.

Sources:
Querying data by Joining two Tables in two database on Different Servers.
Selecting data from two Different Servers in SQL Server.

Answer 2

This way it works. Look at the legend.

select * from 1.[2].[dbo].3

1 = server

2 = base

3 = table

Ex:

select * from srv01.[db_brasil].TB_CIDADES