SQL 跨同一服务器上的多个数据库查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20611310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query across multiple databases on same server
提问by Paddy
I am looking for a way of dealing with the following situation:
我正在寻找一种处理以下情况的方法：
We have a database server with multiple databases on it (all have the same schema, different data).
We are looking for a way to query across all the databases (and for it to be easy to configure, as more databases may be added at any time). This data access must be realtime.
我们有一个数据库服务器，上面有多个数据库（都具有相同的架构，不同的数据）。
我们正在寻找一种跨所有数据库进行查询的方法（并且易于配置，因为可以随时添加更多数据库）。这种数据访问必须是实时的。
Say, as an example, you have an application that inserts orders - each application has its own DB etc. What we are then looking for is an efficient way for a single application to then access the order information in all the other databases in order to query it and subsequently action it.
举个例子，你有一个插入订单的应用程序——每个应用程序都有自己的数据库等。然后我们正在寻找一种有效的方式，让单个应用程序访问所有其他数据库中的订单信息，以便查询它并随后对其进行操作。
My searches to date have not revealed very much, however I think I may just be missing the appropriate keywords in order to find the correct info...
迄今为止，我的搜索并没有透露太多信息，但是我想我可能只是缺少适当的关键字才能找到正确的信息...
采纳答案by Richard
It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.
它不会是有史以来最干净的解决方案，但您可以在“主数据库”（如果您的各个数据库不会保持不变）上定义一个视图，该视图包括来自各个数据库的数据，并允许您执行查询单一来源。
For example...
例如...
CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable
Which allows you to do...
这允许你做...
SELECT * FROM vCombinedRecords WHERE....
When your databases change, you just update the view definition to include the new tables.
当您的数据库发生变化时，您只需更新视图定义以包含新表。
回答by TcKs
You must specify the database name before any database object.
您必须在任何数据库对象之前指定数据库名称。
Single database:
单个数据库：
SELECT * FROM [dbo].[myTable]
Multiple dabases:
多个数据库：
SELECT * FROM [DB01].[dbo].[myTable]
UNION ALL
SELECT * FROM [DB02].[dbo].[myTable]
UNION ALL
SELECT * FROM [DB03].[dbo].[myTable]
回答by Andomar
You can build the union dynamically:
您可以动态构建联合：
select name from sys.databases
and then check if the database has the table:
然后检查数据库是否有表：
select name from [dbname_from_above].sys.tables where name = 'YourTable'
That gives you all databases for the union. You can build the query client side or in dynamic SQL.
这为您提供了工会的所有数据库。您可以构建查询客户端或动态 SQL。
回答by youcantryreachingme
Shooting from the hip here.
从这里的臀部拍摄。
use master;
go
create table #Temp (sourceDBName varchar(128), colA varchar(128), colB varchar(128));
exec sp_MSforeachDB ' USE [?];
insert into #Temp
SELECT DISTINCT
''?'',
tableA.colA,
tableB.colB
FROM tableA JOIN tableB on some_conditions
WHERE someCol LIKE ''%some_term%''
'
select sourceDBName, colA, colB from #Temp order by 1, 2, 3;
drop table #Temp;
This logic should allow you to apply a single query to all databases. To use it though, you will want to add logic to filter out system databases, or explicitly include only the databases you specify. To achieve that, you might like to put this logic into a stored procedure which then returns a result set, so in the end, your call to this logic is a select statement that returns a rowset you can join, filter, etc.
此逻辑应允许您将单个查询应用于所有数据库。但是，要使用它，您需要添加逻辑来过滤系统数据库，或者仅显式包含您指定的数据库。为了实现这一点，您可能希望将此逻辑放入一个存储过程中，然后该存储过程返回一个结果集，因此最后，您对该逻辑的调用是一个选择语句，该语句返回一个您可以加入、过滤等的行集。
