Find out when was a view was accessed within the last x days

Question

I'd like to find out the specific dates and times when a specific view or a group of views was last accessed. How can I query SQL Server to get this information ?
Thank you

Answer 1

Hi @vsslasd ,

There is no way to see queries or views executed in SSMS by default. There are several options though. You could refer below and check whether any of them is helpful to you.

Firstly, if SQL Server hasn't been restarted (and the plan hasn't been evicted, etc.), you may be able to find the query in the plan cache.

SELECT   
t.TEXT QueryName,s.last_execution_time,*  
FROM sys.dm_exec_query_stats s  
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t  
where t.text like '%viewname%'  --update the view name here   

OR

SELECT t.[text], s.last_execution_time  
FROM sys.dm_exec_cached_plans AS p  
INNER JOIN sys.dm_exec_query_stats AS s  
   ON p.plan_handle = s.plan_handle  
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t  
WHERE t.[text] LIKE N'%viewname%'  
ORDER BY s.last_execution_time DESC;  

Secondly, you could try with SQL Server profiler which best suited if you just want to start auditing and you are not interested in what happened earlier. Mainly catch RPC:Completed and SQL:BatchCompleted events for all queries that take over 10 seconds to run, and save the output to a tracefile that you can open up in SQL profiler at a later date.

Please refer more code and details in How to see query history in SQL Server Management Studio.

Thirdly, using SQL Server Audit (Database Engine).

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Have you set up an Audit for access on the views? If not, this information is nowhere to be found, as SQL Server does not track this by default.