Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

 

**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

Total article views: 1143 | Views in the last 30 days: 8
 
Related Articles
FORUM

ReportViewer (web control) - parameter change refresh

ReportViewer (web control) - parameter change refresh

BLOG

Refreshing Availability Group Database with PowerShell

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

FORUM

Database Refresh

Database Refresh

FORUM

server refresh

refresh

SCRIPT

Copy/Refresh a database in the night

Refresh a report database with this script.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones