Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.448 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • April 2 2016
  • 3
  • Database DMVs and Catalog Views SQL Server

How to find out the date of last access to a table or view and execution of a procedure in SQL Server

Views: 9.378
Reading Time: 3 minutes

Hello people,
Good night!

In this post I will show you how to identify the last access date of a table or view and the last execution date of a procedure in SQL Server. This is especially useful for identifying unused tables or even understanding how much they are used.

For that, SQL Server gives us the excellent DMV sys.dm_db_index_usage_statswhich, despite its name, works for both indexed (nonclustered) and HEAP (nonindexed) tables.

A point of attention for anyone using this view is that when the instance is restarted the data is reset.

How to find out the last access date and time of a table or view

With a simple query, we can easily see the last access date of the object, the last access date by read type and the types of readings that were identified by the view.

To identify tables or views that have not been read since the instance was restarted, simply check the records where the last_access IS NULL column is empty.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT
    A.name AS [object_name],
    A.type_desc,
    B.database_id,
    C.name AS index_name,
    (
        SELECT MAX(Ultimo_Acesso)
        FROM (VALUES (B.last_user_seek),(B.last_user_scan),(B.last_user_lookup),(B.last_user_update)) AS DataAcesso(Ultimo_Acesso)
    ) AS last_access,
    B.last_user_seek,
    B.last_user_scan,
    B.last_user_lookup,
    B.last_user_update,
    NULLIF(
        (CASE WHEN B.last_user_seek IS NOT NULL THEN 'Seek, ' ELSE '' END) +
        (CASE WHEN B.last_user_scan IS NOT NULL THEN 'Scan, ' ELSE '' END) +
        (CASE WHEN B.last_user_lookup IS NOT NULL THEN 'Lookup, ' ELSE '' END) +
        (CASE WHEN B.last_user_update IS NOT NULL THEN 'Update, ' ELSE '' END)
    , '') AS operations
FROM
    sys.objects                                 A
    LEFT JOIN sys.dm_db_index_usage_stats       B    ON    B.[object_id] = A.[object_id] AND B.[database_id] = DB_ID()
    LEFT JOIN sys.indexes                       C    ON    C.index_id = B.index_id AND C.[object_id] = B.[object_id]
WHERE
    A.[type_desc] IN ('VIEW', 'USER_TABLE')
ORDER BY
    A.name,
    B.index_id

SQL Server - sys.dm_db_index_usage_stats last table view access date 1

When testing the example above, remember to change the database msdb to your own or leave no database specified if you use the database from the context of your connection.

How to find out the date and time of the last execution of a stored procedure

To get this view, we use the sys.objects DMV to get the list of procedures and functions from the msdb database, do a LEFT JOIN with sys.dm_exec_query_stats, which is where the instance's execution plans are stored and perform a CROSS APPLY with DMV sys.dm_exec_sql_text, which is responsible for storing the commands executed by execution plans in cache.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
    A.name AS [object_name],
    A.type_desc,
    MAX(B.last_execution_time) AS last_execution_time
FROM
    sys.objects    A
    LEFT JOIN (
        sys.dm_exec_query_stats B
        CROSS APPLY sys.dm_exec_sql_text(B.sql_handle) C
    ) ON A.[object_id] = C.objectid
WHERE
    A.type_desc LIKE '%_PROCEDURE'
GROUP BY
    A.name,
    A.type_desc
ORDER BY
    3 DESC,
    1

SQL Server - dm_exec_query_stats last stored procedure execution date

That's it, readers!
Two simple, quick and useful queries in everyday life.

Hug!

sql sql server how to find out last access date last read from table view how to find out stored procedure execution date

sql sql server how to find out last access date last read from table view how to find out stored procedure execution date

sql server how to discover unread tables accessed unused tables not used last accessed date stored table unused not used

sql server how to discover unread tables accessed unused tables not used last accessed date stored table unused not used

tags: sqlsql server

You may also like ...

  • How to remove accent and special characters from a string in SQL Server

    How to remove accent and special characters from a string in SQL Server

  • SQL Server - Rounding numbers according to ABNT NBR 5891

  • SQL Server - NOLOCK vs READPAST: Do you know the difference between the two?

  • Next How to retrieve the largest value between multiple columns in a SQL Server table
  • Previous CLR - Solving the System.Security.Permissions.FileIOPermission problem in SQL Server

Comments

  • Comments3
  • Pingbacks0
  1. John said:
    19 October 2018 to 08: 10

    Does not bring view execution. Can you help me?

    Reply
  2. lu said:
    December 19 from 2017 to 06: 17

    Good,

    Very useful scripts, thanks a lot helped.

    Hugs,

    Reply
  3. Francisco Chagas said:
    3 May 2016 to 14: 01

    Great article, congratulations… it helped me a lot.

    Thank you

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.