How to find unused tables in SQL Server

Question

Is there a way of finding out when the data was last entered into a table? I'm trying to find obsolete tables within my database and would like to know if there is a simple script(s) that I can run?

Answer 1

You could try check the results of querying the sys.dm_db_index_usage_stats Dynamic Management View like this:

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID() 
    AND [object_id] = OBJECT_ID('TableName')

This will return things like the last_user_seek, scan and update dates on the indexes on the table.

Howvever, beware as the stats for the dynamic management view are reset when the server is restarted. The longer the server has been running, the more confidence you can have if the records show no activity.

I personally would also be checking all the source code to check for references to the table in question, and searching all sprocs/UDFs for references too (you can use SQL Search from Red Gate to do this - it's free)

Answer 2

Last time it was queried:

-- Create CTE for the unused tables, which are the tables from the sys.all_objects and 
-- not in the sys.dm_db_index_usage_stats table

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) 
AS ( 
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.all_objects  DBTable 
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
  WHERE DBTable.type ='U' 
     AND NOT EXISTS (SELECT OBJECT_ID  
                     FROM sys.dm_db_index_usage_stats
                     WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate 
FROM UnUsedTables
ORDER BY TotalRowCount ASC

Last time updated:

With Unused_Tables (Table_Name, Row_Count, Created_Date, Last_Modified_Date,
Last_User_Lookup, Last_User_Scan, Last_User_Seek, Last_User_Update) 
AS ( 
  SELECT AO.name AS Table_Name
     ,PS.row_count AS Row_Count
     ,AO.create_date AS Created_Date
     ,AO.modify_date AS Last_Modified_Date
     ,ius.last_user_lookup AS Last_User_Lookup
     ,ius.last_user_scan AS Last_User_Scan
     ,ius.last_user_seek AS Last_User_Seek
     ,ius.last_user_update AS Last_User_Update
  FROM sys.all_objects  AO 
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=AO.name
     LEFT JOIN sys.dm_db_index_usage_stats ius ON OBJECT_NAME(ius.object_id)=AO.name
  WHERE AO.type ='U' 
  
)
SELECT  * FROM Unused_Tables
Where ISNULL(Last_User_Lookup,'1900-01-01')<DATEADD(month, -1, GETDATE()) AND 
      ISNULL(Last_User_Scan,'1900-01-01')<DATEADD(month, -1, GETDATE()) AND
      ISNULL(Last_User_Seek,'1900-01-01')<DATEADD(month, -1, GETDATE()) AND 
      ISNULL(Last_User_Update,'1900-01-01')<DATEADD(month, -1, GETDATE())
ORDER BY Row_Count DESC