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.

Read the SQL Server Database Transaction Log

Who deleted that data?! Or worse -- who dropped that table?!  It really frustrates me when something like this occurs on the servers that I am responsible for... yet I am unaware.  Here is a quick piece to show you that yes, we can read the database transaction log, and we can figure out who made that unauthorized change.

 

First we need to create a test table.

   -- create test table
   CREATE TABLE [Personnel] (
     [UserID] INT IDENTITY(1,1),
     [UserName] VARCHAR(55),
     [Dept] CHAR (25)
   );

Ok. Now let us look in the log file to see that new table.

   -- look in log for that table creation
   SELECT
     [Current LSN],
     [Operation],
     [Transaction Name],
     [SPID],
     [Begin Time]
   FROM  
     fn_dblog(NULL,NULL)
   WHERE 
     [Transaction Name] = 'CREATE TABLE'

Current LSN	Operation	Transaction Name	SPID	BeginTime
00000020:00000f70:002c	LOP_BEGIN_XACT	CREATE TABLE	64	2014/02/05 12:10:41:093

Sweet!  Now let us write something into the table.

   -- write into test table
   INSERT dbo.Personnel (UserName,Dept)
   SELECT 'John Doe','Accounting'
   UNION
   SELECT 'Jane Doe','Mangement'

Look at our data:

   SELECT * FROM dbo.Personnel

Ok. Now we are going to do that unauthorized DELETE.

   /* delete something */
   DELETE dbo.Personnel
   WHERE UserName = 'Jane Doe'

Check the log... it is there.

   -- read the log
   SELECT
      [Transaction ID],
      [Operation],
      [Context],
      [AllocUnitName]
   FROM  
      fn_dblog(NULL,NULL)
   WHERE  
      Operation = 'LOP_DELETE_ROWS'
      AND AllocUnitName = 'dbo.Personnel'

Transaction ID	Operation	Context	AllocUnitName
0000:00000557	LOP_DELETE_ROWS	LCX_HEAP	dbo.Personnel

Now, we need to use that Transaction ID returned above to get the Transaction SID.
This will help us find the actual user who performed this action.

   -- get the transaction sid
   SELECT
      [Transaction ID],
      [Transaction SID],  
      [Operation],
      [AllocUnitName]
   FROM  
      fn_dblog(NULL,NULL)
   WHERE   
      [Transaction ID] = '0000:00000550'

 

Transaction ID	Transaction SID	Operation	AllocUnitName
0000:00000557	0x010500000000000515000000EFEE1C9FBC493493D723D1D8E8030000	LOP_DELETE_ROWS	dbo.Personnel

Ok. This is the gold! Now we use the Transaction SID to figure out who actually performed the delete!

   -- find the offending user
   USE master;
   SELECT SUSER_SNAME(0x010500000000000515000000EFEE1C9FBC493493D723D1D8E8030000) [Login]

Login

MyMachine\MyName

 
Alright. One better. Let us drop that Personnel table.

   -- drop table
   DROP TABLE dbo.Personnel

Just like before, let us get our Transaction ID, and then our Transaction SID.

   -- get transaction id
   SELECT
      [Transaction ID],
      [Transaction SID],
      [Transaction Name],
      [Operation],
      [Description]
   FROM  
      fn_dblog(NULL,NULL)
   WHERE
      [Transaction Name] = 'DROPOBJ'

And now... we find the guy who dropped the table.


   -- pass transaction sid to get user name
   USE master;
   SELECT SUSER_SNAME(0x010500000000000515000000EFEE1C9FBC493493D723D1D8E8030000) [Login]


It is undocumented, so you need to be cautious.  But, it is very easy method for reading into your transaction log files.