Refresh all views in a database

 

Refreshing SQL Server Views Automatically

When working with SQL Server, views can become outdated if the underlying tables or objects they depend on are modified. This is especially true for non-schema-bound views—those that don’t explicitly enforce dependency checks on referenced objects. Fortunately, SQL Server provides tools to refresh the metadata of these views, ensuring that they continue to function correctly.

Script to Refresh All Views in a Database

The following T-SQL script automates the process of refreshing all non-schema-bound views in a selected database. It loops through each eligible view and executes the sp_refreshview stored procedure.

DECLARE @ActualView VARCHAR(255);
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT DISTINCT s.[Name] + '.' + o.[Name] AS ViewName
FROM [sys].objects o
JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID 
WHERE o.[type] = 'V'
  AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
  AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1;

OPEN viewlist;
FETCH NEXT FROM viewlist INTO @ActualView;

WHILE @FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXECUTE sp_refreshview @ActualView;
    END TRY
    BEGIN CATCH
        PRINT 'View ' + @ActualView + ' cannot be refreshed.';
    END CATCH;

    FETCH NEXT FROM viewlist INTO @ActualView;
END;

CLOSE viewlist;
DEALLOCATE viewlist;

This script:

• Selects all user-defined, non-schema-bound views
• Iteratively refreshes each view using sp_refreshview
• Handles errors gracefully using TRY...CATCH

Refreshing a Specific View

If you want to refresh a single view manually, you can run:

EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

Refresh Views Dependent on a Specific Object

Sometimes, you may want to refresh only those views that depend on a particular table or object. Here’s how you can identify and generate sp_refreshview commands for them:

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V'
  AND sed.referenced_id = OBJECT_ID('Person.Person');

Replace 'Person.Person' with the name of the object that has changed. This query will list the commands needed to refresh only the affected views.

Learn More

For more details, see the official Microsoft documentation:
sp_refreshview (Transact-SQL) – Microsoft Docs