Search This Blog

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.
   USE DBName;
   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: