为数据库创建同义词/更改数据库视图指向
我知道 CREATE SYNONYM 不支持数据库，但我希望实现它提供的功能。
我们有数据库 A，其中包含数据库 B 上的表的视图。问题是“数据库 B”并不总是称为“数据库 B”。我们使用数据库项目进行部署，如果没有“数据库 B”，该项目目前会出现“无效对象名称”错误。
目前的解决方法是打开 .dbschema 文件并进行查找和替换。我想另一个选择是创建大量表同义词。
更改多个视图引用的数据库而不单独更改每个视图的最佳方法是什么？
谢谢
I know databases aren't supported by CREATE SYNONYM, but I'm looking to achieve the functionality this would provide.
We've got Database A which contains views to tables on Database B. The trouble is "Database B" isn't always called "Database B". We use database projects for deployments, which at the moment fall over with an "Invalid Object Name" error if there isn't a "Database B".
The workaround at the moment is to open up the .dbschema file and do a find and replace. I guess another option would be to create a load of table synonyms.
What's the best way of changing the database a number of views reference without changing each view individually?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
同义词是做到这一点的好方法。不过，您必须在对象级别创建同义词（正如您所发现的）。执行此操作的一种简单方法是编写一个脚本，该脚本运行 DatabaseB 中的表列表（来自您的示例），并为 DatabaseA 中的每个表创建一个同义词。保持同义词的名称相同，这样视图中的代码就不必更改。例如，您在 DatabaseB 中有 tbl_a、tbl_b 和 tbl_c，您希望脚本最终执行以下操作：
现在，在您的视图代码中，您将始终使用 [otherDb].[tbl_a], [otherDb] .[tbl_b]和[otherDb].[tbl_c]。希望这是有道理的。
Synonyms are a good way to do this. You have to create the synonyms at the object level though (as you've discovered). An easy way to do this would be to write a script that runs through the list of tables in DatabaseB (from your example) and creates a synonym for each one in DatabaseA. Keep the name of the synonym the same so the code in your views doesn't have to change. For instance, you you have tbl_a, tbl_b, and tbl_c in DatabaseB, you'd want your script to eventually do the following:
Now, in your view code, you'll always use [otherDb].[tbl_a], [otherDb].[tbl_b], and [otherDb].[tbl_c]. Hope this makes sense.
去年，我帮助我现在的客户实施了一个非常相似的设计。我们编写了一组自动生成视图的函数和存储过程。每当您需要更改目标数据库时，它都会生成代码来删除并重新创建所有视图。
代码并不太难。它只是使用系统表来生成视图代码。我还编写了一个 Powershell 原型，它使用 SMO 来做同样的事情。关键是让它自动化到只需要一次调用，这样您就可以轻松准确地完成它。
我们还包括一个异常表，它使用表的模式匹配来从视图生成中排除。它包括一个架构列和一个表名称列，每个列都接受 LIKE 模式，因此您可以输入“my_schema”和“%”来排除 my_schema 架构中的所有表。
一个主存储过程接受目标数据库名称并生成整个脚本。生成脚本后，您可以在 SSMS 中运行它，或者也使该部分自动化。
如果您只想生成同义词，整个事情会更容易。我们使用视图，以便我们可以更改列列表等，并使视图数据库在需要时看起来与目标数据库不同。
Last year I helped my current client with the implementation of a very similar design. We wrote a set of functions and stored procedures which generate the views automatically. Whenever you need to change the target database it generates the code to drop and recreate all of the views.
The code wasn't too difficult. It just uses the system tables to generate view code. I also wrote a Powershell prototype that uses SMO to do the same thing. The key is to have it automated to the point of requiring a single call so that you can do it easily and accurately.
We also included an exception table that used a pattern match of tables to exclude from view generation. It included a schema column and a table name column, each of which accepted
LIKEpatterns, so you could put "my_schema" and "%" to exclude all tables in the my_schema schema.One master stored procedure accepted a target database name and would generate the entire script. Once the script is generated you can run it in SSMS or have that part automated as well.
This whole thing would be even easier if you just wanted to generate synonyms. We were using views so that we could change column lists, etc. and have the view DB look different than the target DB where needed.