2

I have to make quite a few changes to my tables. I have numerous views all with schema binding on. Is there a way to turn schema binding off for all views then turn it back on again when I'm done (other than doing it individually)? Any help or direction would be appreciated.

2

Please visit https://stackoverflow.com/questions/18341607/drop-schemabinding-from-all-views-in-a-database

One way would be to highlight the Views node in Object Explorer, then hit F7 (View > Object Explorer Details). On the right will be all of the views; unfortunately you can't filter or add a new column to sort based on whether the view does have SCHEMABINDING. So you'll have to manually select the ones you want (or just select all except the System Views folder), then right-click, Script View As > DROP and CREATE to > New Query Editor Window. Now you can just search that script for SCHEMABINDING, delete or comment out each instance, and then run the script. If some of the views you selected did not have SCHEMABINDING in the first place, then no big deal - they'll just get dropped and re-created too.

(This is much easier, in my opinion, than trying to search and replace CREATE VIEW with ALTER VIEW - since there could be any number of whitespace characters between CREATE and VIEW, and you'll still have to comment or remove the SCHEMABINDING. Though it might not hurt, as part of this exercise, to ensure that the syntax conventions here are consistent, and fix views that don't adhere, e.g. changing CREATE______VIEW to CREATE_VIEW (underscores to represent spaces).)

Note that this will obviously delete any indexes that are associated with these views.

Also note that the generate scripts wizard will also allow you to select all of the views, but not filter on whether they have SCHEMABINDING, and the wizard won't script ALTER but you can choose DROP and CREATE in Advanced settings. You'll still need to adjust the output to remove the SCHEMABINDING and, depending on options selected, you may need to remove any CREATE INDEX statements for indexed views that will now fail.

|improve this answer|||||
  • Thank you so much. I searched and searched and didn't find that answer. I'm going to delete this question after I accept the answer in two minutes. – Missy Jun 17 '17 at 16:48
  • 1
    No worries! You do not have to delete, it can be helpful for someone else in the future. – user120572 Jun 17 '17 at 16:50
  • 1
    Be aware that drop-and-recreate will cause loss of permissions explicitly defined on those objects. – Max Vernon Jun 17 '17 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.