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.
| 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 |
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.
| 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 |
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
Does not bring view execution. Can you help me?
Good,
Very useful scripts, thanks a lot helped.
Hugs,
Great article, congratulations… it helped me a lot.
Thank you