how to identify tables that are not being accessed by the application or used

Question

how to identify unused tables on prod so they can be dropped if the table had index, will user updates column on the index usage stats give information what if the index never gets used and only non clustered indexes exist

Answer 1

You may try to use the system view sys.dm_db_index_usage_stats to check the columns last_user_seek, last_user_scan, last_user_lookup and last_user_updates:

SELECT o.name AS Table_Name, s.* 
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.databases AS d ON d.database_id = s.database_id
INNER JOIN sys.objects AS o ON o.object_id = s.object_id
WHERE d.name = 'DATABASE_NAME' AND o.type = 'U';

Please note that the tracking data will be reset after the SQL server restarts.

Answer 2

Hi,

Please refer to the script in the following article.
https://www.mssqltips.com/sqlservertip/4191/identify-unused-sql-server-tables/

>will user updates column on the index usage stats give information what if the index never gets used and only non clustered indexes exist

The system view sys.dm_db_index_usage_stats will returns counts of different types of index(HEAP,NONCLUSTERED, CLUSTERED)operations and the time each type of operation was last performed.