"If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried." http://msdn.microsoft.com/en-us/library/ms187821.aspx
Due to the risk of Views producing unexpected results when an underlying object has had changes, the easiest thing to do is then refresh all views that don't have schemabinding after all View changes have been implemented.
To use this Stored Procedure, run the attached code over which ever db you want to use, then run ' EXEC dbo.PRefreshViews' when connected to that db.
The PRfeshViews SP selects all user defined views that are not schemabound no matter what schema was used to create them, and refreshes those views.
**UPDATE**
Script now prints the schema + view it's refreshing
Script now handles an error if it occurs when refreshing a view (if a column of a view no longer exists) so that the remaining views can be refreshed, or can pick up several issues with views in one execution.
Script now refreshes views in order of their creation date to handle dependancy. I have no evidence that this is an issue but it's better to be defensive where possible.
*************
Regards,
Cameron Wells
Cameron.g.wells@gmail.com
