Wednesday, February 5, 2014

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 on the sys.dm_db_index_usage_stats DMV:
             http://technet.microsoft.com/en-us/library/ms188755.aspx

Another thing to look for would be the empty tables.  Many times tables are created and developed in production, but never actually implemented.  You can use this newer post of mine to bring back all tables that are empty.  
   http://www.sqlfingers.com/2014/10/list-all-empty-tables-in-your-sql.html

REMEMBER -- just being empty does not necessarily mean it isn't used.  It could be a working table that is purged regularly as it is being used.  Keep that in mind before you go and drop something just because it is empty.

5 comments:

  1. Replies
    1. Glad you're using the site, and very glad to have helped!

      Delete
  2. I think this help to see if the strcuture of the table changed but no if it is used, you can not change a table inlong time but use it

    ReplyDelete