managing views

Unfortunately, there is no certain way to find such information. There are some workarounds but they have so many consequences and dependencies. For e.g. Below script would give you the name of all of your VIEWS and the last execution time

SELECT  V.NAME
,       MAX(DEQS.last_execution_time)
FROM    sys.[views] AS V
        OUTER APPLY ( SELECT    MAX(last_execution_time) last_execution_time
                      FROM      sys.dm_exec_query_stats AS DEQS WITH (NOLOCK)
                                CROSS APPLY sys.dm_exec_sql_text(DEQS.sql_handle)
                                AS DEST
                      WHERE     DEST.text LIKE '%' + V.[name] + '%'
                    ) DEQS
GROUP BY v.[name]

But the caveat is the cache as the lifetime of the output field execution time is tied to the cached plans itself. When a plan is removed from the cache, the corresponding execution time would be eliminated from the output. So one has to be very very careful to use information of that kind to draw conclusion of whether a VIEW is in use.

The next options are run a trace for over a certain duration for e.g. over a month. Not to forget month ends, quarter ends, half year and so on.. according to your environment.

But at the end it all would be a laborious work and It cannot be sorted out in one go.

This is pretty tricky to track down, you can query the executing queries dmvs with something like

IF OBJECT_ID('tempdb..#queries') IS NOT NULL 
    DROP TABLE #queries
GO


SELECT  SUBSTRING(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
            ( ( CASE deqs.statement_end_offset
                 WHEN -1 THEN DATALENGTH(dest.text)
                 ELSE deqs.statement_end_offset
                END - deqs.statement_start_offset ) / 2 ) + 1) AS statement_text
INTO    #queries
FROM    [sys].[dm_exec_query_stats] AS deqs
       CROSS APPLY [sys].[dm_exec_sql_text]([deqs].[sql_handle]) AS dest

SELECT  *
FROM    [#queries] AS q
WHERE   [q].[statement_text] LIKE '%view%'' -- use your view names here if poss

but this could be a bad query to run on a busy production server, it will take a lot of effort to complete.

You could use extended events to catch executing code too but that again has performance considerations.

You may be best simply reviewing the system with the system administrator and carefully renaming views and so on until you identify those that are in use.

Thank you for responding. We have many reports and our LIMS application runs of these views.

When a view is called via a report will the execution time also be updated?

We have been considering going the trace route as you suggested but I was hoping that we would find a magical solution here. :-)

Thank you.