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.
SQL Server – How to Change Schema Binding for All Views
schemasql server
Related Question
- MySQL Schema – How to Dynamically Change Schema to Accommodate Custom Fields
- Postgresql – How to change schema so that account_id reference is unique among 3 tables
- Sql-server – Changing Schema ownership and side effects
- Oracle – Logging in as Different User and Viewing All Schema Tables
- MySQL Schema Design – Help with Database Schema Design
- Database Design – Schema for Financial Database
- How to Replicate Schema Changes from Local Database to Remote – SQL Server
Best Answer
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.