How to give a SELECT in several databases at the same time?

Question

How to give a SELECT in several databases at the same time?

Navigation

#1 by (6 votes)

1

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

sql sql-server

asked by anonymous 28.01.2016 / 22:56

1 answer

Incompatibility of blob types and byte sqlite String concatenation problem and variables in the jquery load function

score 6 · Answer 1

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

1st Example (Query with INNER JOIN .):

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

Example 2 (Query without INNER JOIN .):

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

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

Example 3 (Query on 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 user command, just indicate the full path to the table.

Sources:
Querying data by joining two tables in two databases on different servers. Selecting data from two different servers in SQL Server.