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

vsslasd 556 Reputation points
2020-12-02T17:34:56.323+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-12-03T02:31:11.377+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-02T22:53:01.92+00:00

    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.

    0 comments No comments