Wednesday, March 7, 2012

Best method to identify broken view is schemabind?

I am looking for the best way to know if table changes are going to break a view. Is the best way to do this by using the schemabinding option when creating the view? What are the disadvantages of using schemabinding for a view?

Are there other methods to evaluate views as broken? Can you run a sp_ proc or a custom SQL procedure to validate all views?

Thanks.

Schemabinding is the best way to prevent broken views. It will make sure that dependency information is always up to date.

One method I have used is to write a simple query like:

select 'select ''' + name + '''; select top 1 * from ' + name + ';' + char(13) + char(10) + 'G0'

from sys.views

and then just run the output. Broken views will cause errors that should be easy enough to follow...

No comments:

Post a Comment