Recently, I was tasked with “cleaning up” a very large database on our network – it included hundreds of tables with cryptic names, and I wasn’t able to tell which ones were still being used and which weren’t. There are triggers for INSERT, UPDATE, and DELETE, but no trigger for SELECT, and that’s what I wanted.
However, SQL Server 2005 and later provide something that’s almost as good – the sys.dm_db_index_usage_stats system view. This view has table and index statistics for every table in the database and you can use it to determine when a table was last accessed. Though I initially thought this table only contained index stats, so would be useless against tables without indexes, that’s not the case; it contains tables themselves as well, and calls them “HEAP” indexes. This way, you can see which tables are being scanned against often (a sign that a better set of indexes is needed), or which indexes aren’t being accessed at all and can safely be removed.
Using this data, it’s easy to determine which tables haven’t been accessed since the server was last restarted:
WITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
Since the table is cleared when the SQL service restarts, this will only display the tables not accessed since the last time the server was restarted. Because of this, you’ll need to ensure that the SQL Server has been running for sufficiently long before you rely on this query to see which tables aren’t accessed by users.
Keep in mind that, even if the server has been running for months and a table is still in this list, it may not be safe to delete it. Some tables may be part of year-end or rare processes. This list should be used as a guide to help you figure out what’s safe to delete, and you may even consider renaming objects for a while first, so that any processes that do end up relying on one of these tables can be easily corrected by renaming the objects back.