Tell me more ×
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.
up vote 3 down vote favorite

How do I switch off SCHEMABINDING for a view without recreating it?

share|improve this question

2 Answers

up vote 3 down vote accepted

Yes. It's good that you use SCHEMABINDING (we do always) and sometimes you have to remove it to change a dependent object. Just ALTER the view

ALTER VIEW myView
--Remove this WITH SCHEMABINDING
AS
SELECT ...
GO
share|improve this answer
so did I, but sometimes other objects (functions, views) depend on this one. So it will be good to mark /unmark this flag for a time :). So it is impossible in the current version of db, yes? – garik Mar 28 '11 at 16:43
@garik: correct, I have the same problem. Run ALTER on each dependent object... At any point in time SQL Server will enforce the rules: you can't "switch off" because this would lead to inconsistency – gbn Mar 28 '11 at 16:45
up vote 2 down vote

Won't ALTER VIEW allow for you to get this done? When you create a view you would do:

CREATE VIEW
WITH SCHEMABINDING
AS
SELECT stmt
GO

so, lose the WITH clause:

ALTER VIEW viewname
AS
SELECT stmt
GO

See ALTER VIEW on MSDN

share|improve this answer
The curly 1011010 button formats your code. It's curly brackets on other sites – gbn Mar 28 '11 at 16:25
sigh...when the big red bar came up and said you had already posted an answer, i rushed through the end of posting mine, forgot the format. thanks! – SQLRockstar Mar 28 '11 at 16:26
2  
After a while you stop rep whoring :-) meta.stackoverflow.com/questions/19478/the-many-memes-of-meta/… – gbn Mar 28 '11 at 16:27

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.