SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Refreshing Views

By Cameron Wells,

"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.



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.



Cameron Wells


Total article views: 1187 | Views in the last 30 days: 3
Related Articles

ReportViewer (web control) - parameter change refresh

ReportViewer (web control) - parameter change refresh


Refreshing Availability Group Database with PowerShell

Following last weeks post on Refreshing A Mirrored Database with PowerShell I thought I would write ...


Database Refresh

Database Refresh


Database Refresh Sync

Sync users and roles after restore/refresh


server refresh