在同一服务器上跨多个数据库查询

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...

到目前为止，我的搜索并没有透露太多信息，但是我认为我可能只是为了找到正确的信息而漏掉了适当的关键字……

3 个解决方案

#1

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.

当数据库发生变化时，只需更新视图定义，以包含新表。

#2

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]

#3

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.

这将为union提供所有数据库。您可以构建查询客户端或动态SQL。

#1