CODEWITHSUNDEEP
sql-servercode-generationversion-control
Ben Schwehn
Ben Schwehn

Reputation: 4575

how to handle db schema updates when using schemabinding and updating often

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

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:

this annoys me, but perhaps I simply have to live with it if I want to continue using schemabinding and script updates...

Upvotes: 6

Views: 2284

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

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.

Upvotes: 3

Related Questions