Refreshing views with sp_refreshview

One of the common problems that you would encounter when you are building applications that utilize views is that sometimes there are cases wherein a view gets out of date. This happens when you add a new column to a table a view is refrencing.

To fix this some people would delete the view and recreate it but there is better solution called sp_refreshview. sp_refreshview updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends^{[description from msdn]}.

Here's a remark from MSDN regarding sp_refreshview:

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

The syntax is pretty straight-forward

   EXECUTE sp_refreshview '<view name>'

Once executed the views definition would be updated.

What if I want to execute sp_refreshview on all the views in my database? The answer is to create a cursor that would execute sp_refreshview on each of your view. Below is a script that does exacrly what you need:

--Refresh the underlying metadata of all views
DECLARE @viewName AS VARCHAR(255)

DECLARE listOfViews CURSOR FOR
SELECT [name] 
FROM sysobjects 
WHERE xtype = 'V'


OPEN listOfViews

    FETCH NEXT FROM listOfViews into @viewName

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

        FETCH NEXT FROM listOfViews INTO @viewName
        EXEC sp_refreshview @viewName

    END

CLOSE listOfViews

DEALLOCATE listOfViews

HTH