SQL Server: Tips and Tricks - 2 (2017)

How to generate script to REFRESH all views in a SQL Server Database

Scenario:

Let's say you have made changes to data types of few of the tables and you had views created on those tables. You need to refresh the views definition after that.

Solution:

The below script can be used to generate script to refresh views.

If you have single view, you can use sp_refreshview 'ViewName' to refresh view definition.

To generate the script for all views in a database, you can run below statement.

SELECT 'sp_refreshview '''

+ Schema_name(schema_id) + '.' + NAME + ''''

+ Char(13) + Char(10) + ' GO' AS RefreshViewQuery

FROM sys.views

Click on icon "Results to Text" or press Ctrl+T and then copy the script to run for all views. You can always filter the list in your above select query if you would only want to refresh the views related to one schema or as per view names etc.