As SQL Server environments grow over time, databases accumulate tables that may no longer be actively used. These unused tables, often remnants of past development projects or deprecated business processes, contribute to storage bloat, increase backup times, and create confusion during maintenance and audits. For DBAs managing large or legacy systems, identifying and safely pruning unused objects becomes an essential part of long-term database hygiene. One of the most frequent questions I’ve encountered over my career is: “How can we find out which tables haven’t been used in the last month or three months?” “How can we find out which tables haven’t been used in the last month or three months?” The good news is that SQL Server provides several ways to track usage patterns, though it’s important to note that it doesn’t automatically log access timestamps for every table. Instead, we rely on a combination of Dynamic Management Views (DMVs) and the Query Store (if enabled), along with logging strategies if historical tracking is needed. The most effective and lightweight method, without requiring prior configuration, is to query the DMV for index usage stats, specifically sys.dm_db_index_usage_stats. Dynamic Management Views (DMVs) Query Store DMV for index usage stats sys.dm_db_index_usage_stats This DMV tracks how often indexes — including heap tables (which have index_id = 0) — are used for reads and writes since the last SQL Server service restart. Every time a table or index is accessed via SELECT, INSERT, UPDATE, or DELETE, the corresponding counters in this DMV are updated. Although this DMV only persists data since the last server restart or database detach, in a stable production environment where restarts are infrequent, it’s a very helpful source. To identify potentially unused tables, we join this DMV with the sys.tables and sys.indexes metadata views to get human-readable table names and last access dates. Here's a simple query to get you started: sys.tables sys.indexes SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    MAX(ius.last_user_seek) AS LastSeek,
    MAX(ius.last_user_scan) AS LastScan,
    MAX(ius.last_user_lookup) AS LastLookup,
    MAX(ius.last_user_update) AS LastUpdate
FROM 
    sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    LEFT JOIN sys.indexes i ON t.object_id = i.object_id
    LEFT JOIN sys.dm_db_index_usage_stats ius 
        ON i.object_id = ius.object_id 
        AND i.index_id = ius.index_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.name, s.name
ORDER BY 
    MAX(ius.last_user_seek) DESC; SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    MAX(ius.last_user_seek) AS LastSeek,
    MAX(ius.last_user_scan) AS LastScan,
    MAX(ius.last_user_lookup) AS LastLookup,
    MAX(ius.last_user_update) AS LastUpdate
FROM 
    sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    LEFT JOIN sys.indexes i ON t.object_id = i.object_id
    LEFT JOIN sys.dm_db_index_usage_stats ius 
        ON i.object_id = ius.object_id 
        AND i.index_id = ius.index_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.name, s.name
ORDER BY 
    MAX(ius.last_user_seek) DESC; This query returns each table’s last known interaction via a seek, scan, lookup, or update operation. If all four columns return NULL, it suggests the table has not been used since the last server restart — or possibly even longer, if restarts are infrequent. You can then filter this result further to look for tables untouched in the last 30 or 90 days: -- Find tables not accessed in the last 90 days
WHERE 
    MAX(ISNULL(ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update)) < DATEADD(DAY, -90, GETDATE()) -- Find tables not accessed in the last 90 days
WHERE 
    MAX(ISNULL(ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update)) < DATEADD(DAY, -90, GETDATE()) If you need longer historical tracking beyond the server's uptime, you must use Query Store, custom auditing, or even Extended Events. For environments with Query Store enabled, you can query it to determine which objects have been referenced in the last 90 days by examining execution statistics, though Query Store tracks queries, not directly the tables themselves. In this case, you’d extract query texts and infer which tables they reference — a useful but more complex process. Query Store custom auditing Extended Events Another approach is to implement a lightweight custom auditing solution. You can create a DDL trigger or schedule a job that logs object access using metadata from system views and periodically stores it in an admin table. Over time, this creates a usage history for you to analyze. Conclusion In conclusion, identifying unused tables in SQL Server is a mix of art and science. The sys.dm_db_index_usage_stats view provides immediate insights for recent access, and for longer-term tracking, Query Store or custom auditing becomes necessary. Before dropping any "unused" table, always double-check for stored procedures, views, and application code references. What appears unused in the database engine may still be tightly coupled to business logic elsewhere. Still, taking the time to review and validate table usage is one of the most practical ways to optimize your SQL Server environment, improving performance, reducing clutter, and providing your team with a cleaner canvas for the future. sys.dm_db_index_usage_stats

How to Identify Tables Not Used in the Last Month or Three Months in SQL Server

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Database Health Check: Understanding and Tuning Core Server Resources

Stop Hacking SQL: How to Build a Scalable Query Automation System

Spring Boot Audit Logs: Capture Every API Action Without Writing Boilerplate Code

Is JOIN Faster Than Correlated Subqueries? Taking a Look and Subsequently Debunking the Myth

Storing JSON and XML in SQL Databases: An Essential Guide

Introducing Agentic Postgres, the Database for AI Agents

Database Health Check: Understanding and Tuning Core Server Resources

Stop Hacking SQL: How to Build a Scalable Query Automation System

Spring Boot Audit Logs: Capture Every API Action Without Writing Boilerplate Code

Is JOIN Faster Than Correlated Subqueries? Taking a Look and Subsequently Debunking the Myth

Storing JSON and XML in SQL Databases: An Essential Guide

Introducing Agentic Postgres, the Database for AI Agents

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps