经常使用模式绑定和更新时如何处理数据库模式更新
我正在使用 MS SQL Server 数据库并使用大量视图（与 O/R 映射器一起使用）。有点烦恼的是，我想
- 使用脚本进行模式绑定
- 更新（部署在服务器上并放入源代码控制系统），
但遇到这样的问题：每当我想向表中添加一列时，我都必须首先删除引用该表的所有视图，更新该表，然后重新创建视图，即使视图不需要更新。这使得我的更新脚本变得更长，而且，查看源代码控制系统中的差异，很难看出实际的相关更改是什么。
有更好的方法来处理这个问题吗？
我仍然需要能够使用简单且源可控的 sql 更新。 SQL Server Management Studio 中包含的代码生成器会很有帮助，但我在使用 SQL Server Management Studio 时遇到了问题，因为它倾向于创建不指定某些索引或（默认）约束名称的代码。但是当我在不同的系统上运行脚本时，我希望拥有相同的数据库，包括所有约束的名称等，这样我以后更新这些约束时就不必跳过循环。
那么也许更智能的 SQL 代码生成器会是一个解决方案？
我现在的工作流程是：
- 在查询编辑器中键入
alter table语句 - ，检查是否收到类似“
cannot ALTER 'XXX'，因为它被对象 'YYY' 引用的错误语句”。” - 使用 SQL Server Managment Studio 为引用对象编写
create代码， - 在 alter 语句之前插入
drop语句，并在 - 检查
drop后创建语句> 语句创建错误并重复
这让我烦恼，但如果我想继续使用模式绑定和脚本更新，也许我只需要忍受它......
I'm using a MS SQL Server db and use plenty of views (for use with an O/R mapper). A little annoyance is that I'd like to
- use schema binding
- update with scripts (to deploy on servers and put in a source control system)
but run into the issue that whenever I want to e.g. add a column to a table, I have to first drop all views that reference that table, update the table, and then recreate the views, even if the views wouldn't need to be updated otherwise. This makes my update scripts a lot longer and also, looking the diffs in the source control system, it is harder to see what the actual relevant change was.
Is there a better way to handle this?
I need to still be able to use simple and source-controllable sql updates. A code generator like is included in SQL Server Management Studio would be helpful, but I had issues with SQL Server Management Studio in that it tends to create code that does not specify the names for some indices or (default) constraints. But I want to have identical dbs when I run my scripts on different systems, including the names of all contraints etc, so that I don't have to jump through loops when updating those constraints later.
So perhaps a smarter SQL code generator would a solution?
My workflow now is:
- type the
alter tablestatement in query editor - check if I get an error statement like "
cannot ALTER 'XXX' because it is being referenced by object 'YYY'." - use SQL Server Managment Studio to script me
createcode for the referenced object - insert a
dropstatement before the alter statement and create statement after - check if the
dropstatement creates error and repeat
this annoys me, but perhaps I simply have to live with it if I want to continue using schemabinding and script updates...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您至少可以通过查询一些动态管理函数和系统视图来查找依赖项来消除“检查是否出现错误”步骤。 这篇文章很好地解释了如何做到这一点。除此之外，我认为你是对的，你不能通过模式绑定鱼与熊掌兼得。
另请记住，删除/创建视图将导致您失去授予这些对象的任何权限，因此这些权限也应包含在您的脚本中。
You can at least eliminate the "check if I get an error" step by querying a few dynamic managment functions and system views to find your dependencies. This article gives a decent explanation of how to do that. Beyond that, I think you're right, you can't have your cake and eat it too with schema-binding.
Also keep in mind that dropping/creating views will cause you to lose any permissions that were granted on those objects, so those permissions should be included in your scripts as well.