将 UNION 视图重写为动态视图

Question

我已经为此工作了几天，但找不到合适的解决方案，希望有人能提供帮助。

我们有几个结构相同的数据库服务器（SQL Server），通过链接服务器连接。我们有一堆跨所有服务器联合数据的视图：

CREATE VIEW Things_view AS
  SELECT id, thing
    FROM server1.database1.dbo.stuff
  UNION ALL
  SELECT id, thing
    FROM server2.database2.dbo.stuff
  UNION ALL
  SELECT id, thing
    FROM server3.database3.dbo.stuff

这有几个问题：

• 当我们添加服务器时，我们必须修改 意见。
• 我们的开发环境需要 相同数量的数据库和假链接 服务器，以便我们可以安装这些 意见。
• 有些服务器没有 包括来自其他一些服务器的数据。今天，我们通过这些服务器上的自定义视图来做到这一点，但这是一个部署噩梦。

所以我希望动态地重写它。目标是：

• 允许开发环境 包含不同数量的数据库。他们很可能 一台服务器上有 2 个或更多数据库。
• 为每个服务器提供一种方法 指定哪些其他服务器 包括在结果中。
• 最小化复杂性，以便 解决方案仍然有些 可维护。
• 创建一种解决方案 源代码受控并用于所有 服务器。

最重要的是：

• 将其公开为具有相同内容的视图 名称，这样我们就不必重写我们的 整个应用程序。

当然，我不能在视图或函数中执行动态 SQL。我尝试将其重写为存储过程，然后使用 OPENROWSET技巧从视图查询存储过程。这不起作用，因为我必须使用数据库名称完全限定过程，这将需要更多动态 SQL（因为数据库名称不同）。然后我考虑在主数据库上使用同义词，但这违反了维护和源代码控制目标。

我完全可以创建一个包含服务器名称和数据库名称以及包含标志（如果需要）的新表。事实上，这是理想的，因为它集中了每个环境的配置。

我对此完全困惑不解，现在我向互联网求助，请求帮助！

Answer 1

一种可能的解决方案是创建一个调整视图的存储过程。例如（未经测试）：

create procedure dbo.RecreateView
as
declare @sql varchar(max)

select @sql = IsNull(@sql + 'union all ','') +
              'select * from ' + name + '.dbo.YourView '
from   sys.databases
where  name like 'DbNamePrefix%'

set @sql = 'create view dbo.YourView as ' + @sql
exec (@sql)

您可以在添加数据库后调用存储过程，甚至可以从计划任务中调用存储过程。

Answer 2

如果您使用最新的 SQL Server，使用 Synonym 将是一个不错的选择

Answer 3

如果您有一个跟踪服务器和数据库的表，您可以在该表的触发器中动态构建视图。

看看这个对另一个问题的回答。它建议同样的事情，并有一些示例代码来更改触发器中的视图定义。

动态表设计（通用查找表），需要一个很好的查询来获取值

它可能适合你。

Answer 4

这是我的建议。创建一个表来存储特定服务器上每个视图的配置。
然后根据这些配置编写动态sql来创建视图脚本。
然后在服务器上运行查看脚本。
当您想要添加新服务器时，请将其添加到配置表中，该配置表可以有一个触发器来自动为正在更改的视图重新创建脚本。或者从表中重新创建脚本并运行它们成为每个部署的标准部分。

Answer 5

这听起来像是数据仓库的工作... http://en.wikipedia.org/wiki/Data_warehouse 您需要它按设定的时间表运行，这样数据就不是实时的。这是唯一的缺点。

使用类似于下面的文件从每个 server[n].database.tablename 加载数据。

--Here is how you would administer the script.  One single script that you would only need to modify when you added/removed servers.
So you can easily add this to source control or whatever. You can also do all this, without linked servers, if you look at some ETL tools. like SSIS

insert into dataserver1.datawarehouse.tablename select *,'svr1,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr2,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr3,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr4,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr5,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr6,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr7,'db','tablename' from svr1.db.tablename
insert into dataserver1.datawarehouse.tablename select *,'svr8,'db','tablename' from svr1.db.tablename

所以你需要创建许多插入语句。您拥有的每个服务器/数据库/表组合都有一个。

insert into dataserver1.datawarehouse.tablename select *,'svr1,'db','tablename' from svr1.db.tablename

因此，当您将数据加载到这个新的数据库表中时，您只需附加到每一行原始服务器名/数据库名/表名所以......你的数据看起来像这样

col1 col2 col3 srvnam dbname tbl
foo  bar  some MSSQL1 master mycooltable
foo  bar  some MSSQL2 other  mycooltable

现在，你所要做的就是查询一个表，它会非常快，而且干净。