The purpose of schema binding a view is to ensure that the base tables referenced in the view cannot be modified in a way that would affect the view definition.

This is normally a good thing. After all, you don’t want someone coming along and dropping a table that your view depends on, do you?

But what if you need to make changes to one or more tables referenced by your view?

In this case, you could remove schema binding from a view, make the changes to the base table/s, then reapply schema binding.

There are two ways to remove schema binding from a view:

• Alter the view so that its definition no longer specifies schema binding.
• Drop the view (then re-create it without schema binding if required).

Example of a Schema-Bound View

First, here’s an example of a schema bound view:

CREATE VIEW dbo.v_Cats
WITH SCHEMABINDING
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO

We know it’s a schema bound view because it contains WITH SCHEMABINDING in its definition. To remove schema binding, all we need to do is remove that bit.

Option 1 – Alter the View

To remove schema binding from this view by altering it, we can use the following code:

ALTER VIEW dbo.v_Cats
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO

All we did was change CREATE to ALTER, and remove WITH SCHEMABINDING.

Option 2 – Drop the View

Here’s an example of dropping the view, then re-creating it without schema binding:

DROP VIEW IF EXISTS dbo.v_Cats;
GO

CREATE VIEW dbo.v_Cats
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO

In this case, I used the DROP IF EXISTS syntax, which prevents an error from occurring in the event the view doesn’t exist.