SQL Server: Tips and Tricks - 2 (2017)

How to check when the view definition was refreshed in SQL Server

Scenario:

We know that if the data type changes for columns for objects which are used in the view, we have to refresh the view definition by using sp_refreshview to update metadata for columns used in the view.

Sometime we need to know what was the last time the view definition was altered or refreshed.

Solution:

You can use system view to get that information.

Select * from sys.views

https://2.bp.blogspot.com/-Az49yxM_IvI/WI-BYDFXjTI/AAAAAAAAf-s/O0MiNBsRoEsSInPH9CtyHKUqI1daObRgwCLcB/s640/sp_refereshView_SQL.PNG



Notice that if the view is created and never been altered or definition never been refreshed then created_date=modify_date.

Once you will refresh view definition or alter the view, then modify date will be the date time when the view as altered or definition was refreshed.