在单独的数据库中查询多个相同表的最有效方法

小编典典

在单独的数据库中查询多个相同表的最有效方法

sql

我有一台服务器（SQL Server 2005），具有多个存档数据库（每季度1个，可追溯到8年），这些数据库在结构上都相同。

我经常需要在跨越n个数据库的特定日期范围内进行查询，通常n小于1-3，但是有可能我需要查询整个数据库。

从代码清洁度和性能角度来看，有什么想法是最有效的方法吗？

当前的解决方案是临时的，有一个视图集合跨越所有或仅是最新的数据库，其他解决方案是生成动态SQL，该SQL指出哪些DB包含了所寻找的数据。

显然，理想的解决方案是对表进行分区，但是我不能这样做，因为它是第三方提供的数据库

戴夫

编辑：我不能合并数据库，因为它们是受第三方控制的，总数据大小约为50GB，因此并不庞大，最大的表每季度包含约150万行

EDIT2：数据仓库从长远来看绝对是正确的解决方案（已在计划中），但我今天不能这样做:(

阅读 225

2021-03-23

共1个答案

小编典典

一种方法是：使用sp_msForEachDb。

- 第1轮 - - - -

使用varchar参数调用此系统过程。（实际上，这比这更麻烦，如果您想知道它的实际作用，请检查master数据库中的代码。）该参数必须是一大段动态代码-
例如，

DECLARE @DemoParameter varchar(1000)
SET @DemoParameter = 'SELECT MyCol from MyTable where CreatedOn between ''Jan 1, 1980'' and ''Dec 21, 2012'''
EXECUTE sp_msForEachDb @DemoParameter

这将对SQL实例上的每个数据库运行查询，每个数据库返回一个集合-除了那些没有必要表的数据库（会引发错误）（特别是系统数据库）。这导致我们…

-第2轮---------

在动态代码中，随着数据库在问号的所有实例上进行迭代？将被替换为当前正在处理的数据库的名称。您可以使用它来过滤哪些数据库将被处理，哪些数据库将不被处理。还请注意，例程
不会更改“当前”数据库，您必须自己执行此操作。这给了我们这样的代码：

SET @DemoParameter = '

IF ''?'' like  ''%Foo%''
 BEGIN
    USE ?
    SELECT MyCol from MyTable where CreatedOn between ''Jan 1, 1980'' and ''Dec 21, 2012''

'

这只会对名称包含字符“ foo”的那些数据库运行查询。可能您可以检查每个数据库中是否存在该表。其他方法表明自己。

这将为每个数据库散发一个数据集，如果您需要一个整齐有序的数据集，这对您没有太大帮助，那么我们可以…

-第三轮------------

简要地说：创建一个临时表，并从动态查询中填充它。如下所示，您可以包含数据库名称，也可以包含服务器名称-在跨多个服务器查询数十个数据库中的丢失数据时非常有用。

创建（或清除）临时表：

IF object_id('tempdb.dbo.##Foo') is null
    CREATE TABLE ##Foo
     (
       ServerName         varchar(100)  not null
      ,DBName             varchar(100)  not null

      --  Add your own columns here
      ,MyCol              int  not null
     )

ELSE
    --Option: Delete this line to not clear on each run
    TRUNCATE TABLE ##Foo

运行代码（这是我的主模板，您可以在此处轻松使用@DemoParameter）：

EXECUTE sp_msForEachDB '
IF ''?'' like  ''%Foo%''
 BEGIN
    USE ?

    INSERT ##Foo
     select @@servername, db_name()
       ,MyCol
      from MyTable
 END
'

…这应该会与您的数据一起生成一个临时表。测试一下，我编写此代码时并没有实际测试代码，而且Typso会渗入。（＃temp表应该与##
temp一样好，我通常会在临时系统支持问题上这样做）

2021-03-23