CODEWITHSUNDEEP
sqlsql-server-2008
Neil Knight
Neil Knight

Reputation: 48547

How to find unused tables in SQL Server

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?

Upvotes: 7

Views: 16398

Answers (5)

Eric Rouach
Eric Rouach

Reputation: 1

The main problem is that data from the dmv's is accurate from last server restart. I developed the following for having a "restart-proof" solution: https://github.com/MadeiraData/MadeiraToolbox/blob/master/Health%20Check%20Scripts/GetTableUsage_RestartProof_AllDatabases.sql

Upvotes: 0

Francesco Mantovani
Francesco Mantovani

Reputation: 12237

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

Upvotes: 4

Kristen Waite
Kristen Waite

Reputation: 1465

If anyone is looking for all unused tables in a database (more the title's request than the question body), this guy had a good query to bring in all unaltered tables in a database. In this case, "unaltered" is any table without an entry in sys.dm_db_index_usage_stats (again, as with AdaTheDev's answer, only since the last sql server reboot).

SELECT OBJECTNAME = Object_name(I.object_id), 
       INDEXNAME = I.name, 
       I.index_id 
FROM   sys.indexes AS I 
       INNER JOIN sys.objects AS O 
               ON I.object_id = O.object_id 
WHERE  Objectproperty(O.object_id, 'IsUserTable') = 1 
       AND I.index_id NOT IN (SELECT S.index_id 
                              FROM   sys.dm_db_index_usage_stats AS S 
                              WHERE  S.object_id = I.object_id 
                                     AND I.index_id = S.index_id 
                                     AND database_id = Db_id(Db_name())) 
ORDER  BY objectname, 
          I.index_id, 
          indexname ASC

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

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)

Upvotes: 9

Randy Minder
Randy Minder

Reputation: 48402

If this is important to your application(s) and/or company, and the tables were designed correctly, then each table should have a column called something like 'LastModifiedTime'. You can query this table to determine which tables are obsolete.

Upvotes: 0

Related Questions