In this article, I am going to demonstrate how we can identify the unused tables of a given SQL database. As a database administrator, we must keep an audit of the database objects. You are administrating a database that has hundreds of tables and some of them are huge. While upgrading the application, usually new tables are added to the database, and required data will be copied from old tables to new tables. We will discuss that in some other article.

The problem is after an application upgrade, the old tables are left unattended. If those tables are large, they will add an overhead in database maintenance as well as they occupied unnecessary space in database. These scenarios become very complicated when the database is on cloud. You must spend unnecessary money on the cloud storage.

This article explains two methods that can be used to identify the unused tables in any database.

• Fetch the list of tables with specific suffix.
• Find the index usage of a table.

Let us understand different methods to get the list of tables with specific suffix.

Fetch the list of tables that have a specific suffix

Sometimes, before making changes in an existing table structure,  developers used to rename the original table. There is certain process in place and if the process is being followed, it becomes easy to identify the obsolete and unused tables. All you must do is to query sys.tables DMVs and fetch the table with specific prefix. For example, in my organization, before making any changes in structure of existing table, we create a new table with changes, copy data from original table to new table, and finally rename the original and new tables. We use _old_dateofchange as a suffix of the table name. Suppose we want to alter the customer table; the old table will be renamed as customer_old_20240209.

The query to fetch the table with a suffix that uses old is shown below:

;with UnUsedTables_WithSuffix (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) 
AS ( 
  SELECT [Objects].name AS TableName
     ,[Partition].row_count AS TotalRowCount
     ,[Objects].create_date AS CreatedDate
     ,[Objects].modify_date AS LastModifiedDate
  FROM sys.all_objects  [Objects] 
     JOIN sys.dm_db_partition_stats [Partition] ON OBJECT_NAME([Partition].object_id)=[Objects].name
  WHERE [Objects].type ='U' 
)
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate 
FROM UnUsedTables_WithSuffix where tablename like  '%_Old%'

When we run this, we can see the query output.

As you can see, the query gets the list of tables with _old_20240209 suffix along with the record counts. You can also use information_schema.tables and sys.tables catalog views to get the list of tables with specific suffix. Here is the query

use StackOverflow2010
go
select 'Output of information_schema.tables' [Output of ],* from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%_old%'
go
/*sys.tables*/select 'Output of sys.tables' [Output of ],object_id, name, type_desc, create_date,modify_date from sys.tables
where name like '%_old%'
go

Output

Let us take a look at another method.

Check the index usage stats

We can find the unused tables by querying a dynamic management view named sys.dm_db_index_usage_stats. The  sys.dm_db_index_usage_stats DMV provides the details of the index operations performed on any table. Note that, When SQL Server restarts, it always removes data from sys.dm_db_index_usage_stats DMV. If you are planning to maintain list of unused tables for prolonged periods, make sure you dump the data in a physical table before SQL restarts.

In the script, first we must get the last restart time of SQL Server. As we know, when we restart the SQL Server, a TempDB recreates automatically hence, we can use create date of TempDB. The query to get SQL Server start time will be following:

declare @SystemStartDate datetime
set @SystemStartDate =(select create_date from sys.databases where name='TempDB')

In the next part of script, we will get the list of tables that are not accessed after SQL Server reboot. We will get the list of tables on which the user seeks, user scan , or user lookup was not performed after SQL Server reboot.  The script is below:

declare @SystemStartDate datetime
set @SystemStartDate =(select create_date from sys.databases where name='TempDB')
;With Unused_Tables (Table_Name,  Created_Date, LastModifiedDate,
LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate) 
AS ( 
  SELECT [Objects].name AS TableName
     ,[Objects].create_date AS CreatedDate
     ,[Objects].modify_date AS LastModifiedDate 
     ,[IndexUsage].last_user_seek AS LastUserSeek
 ,[IndexUsage].last_user_scan AS LastUserScan
      ,[IndexUsage].last_user_lookup AS LastUserLookup
     ,[IndexUsage].last_user_update AS LastUserUpdate
  FROM sys.all_objects  [Objects] 
     LEFT JOIN sys.dm_db_index_usage_stats [IndexUsage] ON OBJECT_NAME([IndexUsage].object_id)=[Objects].name
  WHERE [Objects].type ='U' 
  
)
SELECT  '' + @SystemStartDate + '' SQLStartTime,* FROM Unused_Tables
Where ISNULL(LastUserLookup,'1900-01-01')<@SystemStartDate AND 
      ISNULL(LastUserScan,'1900-01-01')<@SystemStartDate AND
      ISNULL(LastUserSeek,'1900-01-01')<@SystemStartDate AND 
      ISNULL(LastUserUpdate,'1900-01-01')<@SystemStartDate

Now, let us simulate the scenario for clear understanding. For demonstration, I have restored stackoverflow2010 database. You can download it from here.

First,  let us restart the SQL Services. You can read How to stop and start SQL Server services article to learn different methods to restart SQL Server. Once services are restarted, execute the query that I have shown above to fetch the list of unused tables.

Here is the screenshot of the query output

As you can see, after services are restarted, the query is showing all tables because the index usage details from the view is wiped out. Now, execute SELECT query on [Posts],[Users], and [comments] table.

select count(1) from [Posts] 
select count(1) from [Users]
select count(1) from [comments]

Now, Execute the following query to fetch the table usage. Here I have removed the filter on LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate table so we can see the used and unused both tables.

use StackOverflow2010
go
declare @SystemStartDate datetime
set @SystemStartDate =(select create_date from sys.databases where name='TempDB')
;With Unused_Tables (Table_Name,  Created_Date, LastModifiedDate,
LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate) 
AS ( 
  SELECT [Objects].name AS TableName
     ,[Objects].create_date AS CreatedDate
     ,[Objects].modify_date AS LastModifiedDate 
     ,[IndexUsage].last_user_seek AS LastUserSeek
 ,[IndexUsage].last_user_scan AS LastUserScan
      ,[IndexUsage].last_user_lookup AS LastUserLookup
     ,[IndexUsage].last_user_update AS LastUserUpdate
  FROM sys.all_objects  [Objects] 
     LEFT JOIN sys.dm_db_index_usage_stats [IndexUsage] ON OBJECT_NAME([IndexUsage].object_id)=[Objects].name
  WHERE [Objects].type ='U' 
  
)
SELECT  '' + @SystemStartDate + '' SQLStartTime,* FROM Unused_Tables
order by LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate asc

Screenshot of output:

As you can see, the index scan operation is performed on [Posts],[Users], and [comments] table which indicates that the [Posts],[Users], and [comments] table are in use.

Summary

In this article, we learned the importance of identifying and dropping the unused tables. We understand how to find the list of unused tables using T-SQL scripts. Moreover, we also learned how the script works and helps to find unused tables.