Find the unused database tables in SQL Server
I have a customer right now that needs a LOT of cleanup... BIG fragmentation! The tables have not been properly maintained, and the fragmentation really is tremendous. We backed up the db and restored it to a dev server so that I could test out my 'solution'. About 3 hours into it, I realized that I probably needed to revisit my approach. At the very minimum, I wanted to exclude the older, unused objects. No sense spending any time on the tables they are not using.
How do I know which objects are not being used anymore? This is a very quick way to show me all of the tables that have NOT been touched since the last service restart.
SELECT
ao.[name] [Table],
s.[name] [Schema],
[create_date] [Created],
[modify_date] [LastModified]
FROM
sys.all_objects ao JOIN sys.schemas s
ON ao.schema_id = s.schema_id
WHERE
OBJECT_ID NOT IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stats
)
AND [type] = 'U'
ORDER BY
[modify_date] DESC
Check this out for more detailed information: