在不同环境中具有不同数据库名称的跨数据库查询？ [英] Cross-database queries with different DB names in different environments?

问题描述

如何处理不同环境中的跨数据库查询。例如，db1-development和db2-development，db1-production和db2-production。

How would you handle cross database queries in different environments. For example, db1-development and db2-development, db1-production and db2-production.

如果我想在开发中从db2到db1进行跨数据库查询，我可以使用完全限定名[db1-development]。[schema]。[表]。但是如何在不同的环境之间维护查询和存储过程？ [db1-development]。[schema]。[table]因为数据库名称不同而不能在生产中使用。

If I want to do a cross-database query in development from db2 to db1 I could use the fully qualified name, [db1-development].[schema].[table]. But how do I maintain the queries and stored procedures between the different environments? [db1-development].[schema].[table] will not work in production because the database names are different.

我可以看到搜索和替换作为可能的解决方案但我希望有一个更优雅的方式来解决这个问题。如果有db特定的解决方案，我使用的是SQL Server 2005.

I can see search and replace as a possible solution but I am hoping there is a more elegant way to solve this problem. If there are db specific solutions, I am using SQL Server 2005.

推荐答案

为什么dev和prod之间的数据库名称不同？显然，如果它们是相同的，它是最简单的。

Why are the database names different between dev and prod? It'd, obviously, be easiest if they were the same.

如果是一个共享的表，那么你可以创建一个视图 - 只需要你

If it's a single table shared, then you could create a view over it - which only requires that you change that view when moving to production.

否则，您需要为对象创建SYNONYM ，并确保始终引用该对象。你仍然需要更改SYNONYM创建脚本，但是这可以很容易地在构建脚本中完成。我认为。

Otherwise, you'll want to create a SYNONYM for the objects, and make sure to always reference that. You'll still need to change the SYNONYM creation scripts, but that can be done in a build script fairly easily, I think.

这篇关于在不同环境中具有不同数据库名称的跨数据库查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！