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

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.

All materials on the site are licensed Creative Commons Attribution-Sharealike 3.0 Unported CC BY-SA 3.0 & GNU Free Documentation License (GFDL)

If you are the copyright holder of any material contained on our site and intend to remove it, please contact our site administrator for approval.