SQL Server / Remove SCHEMABINDING from the multiple VIEWs without DROP and CREATE statements

What is the SCHEMABINDING within VIEW?
It simple binds the view to the schema of the underlying objects. Tables behind the View cannot be altered in any way which could affect view definition.

You can find more at this MS article:


There is a easy way to remove SCHEMABINDING, you just need ALTER VIEW statement. But what if you have 100 views that needs to be altered?

Well there are no SWITCH OFF button for SCHEMABINDING in SQL Server.

I had same situation and I wrote this simple script which will generate ALTER VIEW statements WITHOUT SCHEMABINDING for you for every single VIEW you have on your SQL Instance.

I used OBJECT_DEFINITION in my script which returns the Transact-SQL source text of the definition of a specified object.

You can find more at this MS article:

You can use “Results To Text” SSMS option to see your results. You might need to adjust “Maximum number of characters” (Max is 8192) in case your VIEW ALTER statements are longer than the default value.

Tools –> Options –> Query Results –> Results to Text –> Max numbers of characters displayed in each column = 8192


You will get something similar to following:

If you prefer you can pass those ALTER statements to the variable and execute them instantly or you can simply choose which one you want to run. 

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *