Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to simply change the length of a column within a table through SQL Server Management Studio, but receive a 'Validation Warning':

'MyTable' table
-Warning: The following schema-bound objects will be modified: 
 -View 'dbo.MyViewName': indexes and schema binding will be removed.

I remember from experience that if I actually go through with this, the binding will not be readded. In the past I've had to manually write a script to re-add the view. This doesn't seem right - why would it just remove it without re-adding it at the end of the operation?

It makes me think I don't have something set up correctly, or am going about this the wrong way - am I? Or is it 'correct' procedure to have to manually re-add things in this way after table changes are made?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Creating a view with WITH SCHEMABINDING locks the underlying tables and prevents any changes that may change the table schema.

To change the base table you need to remove the SCHEMABINDING option from the objects or drop and recreate these objects once the table has been altered.

To find out if the view is schema bound use below tsql:

select objectproperty(object_id('view_name'), 'IsSchemaBound') as SchemaBound

To remove the with schemabinding option, just alter the view defination :

alter view schemaname.tablename
-- with schemabinding (remove it )
as
select some_column_name
from schema_name.table_name

Then make changes to the table and then alter the view again now with schemabinding

alter view schemaname.tablename
with schemabinding --(adding back )
as
select some_column_name
from schema_name.table_name

from BOL :

SCHEMABINDING :

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

share|improve this answer
    
That however still permanently removes the indexes on the view. You also need to script those out and recreate them. –  Sebastian Meine Aug 19 '13 at 19:59
    
@SebastianMeine Good point and I forgot to add it .. You cannot have an index on a view without schemabinding, so yes the indexes are removed and when you add schemabinding back and you have to re-create the indexes. So better to scrip them first. –  Kin Aug 19 '13 at 20:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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