Introduction

You have a database with a lot of tables. Some of them are used, but many are obsolete, not used anymore - and most likely will not be used in the future. They make schema more complicated for understanding, plus they make database’s size bigger, backups and check DB slower. You want to clean them up, remove unnecessary tables and make a database cleaner.

Resolution

We will use dynamic management views such as sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats. They have a wealth of information regarding usage of indexes, which we can be grouped into table level then. Because we don’t want to deal with lengthy scripts, we will use a stored procedure called ShowTableUsage. It is specifically designed for this purpose of finding unused tables within a database. Then, we will use a SQL Server Audit feature to verify the results.

Steps Overview

Here is how the process will look like:

1. Use ShowTableUsage procedure to see current metadata records
2. Schedule ShowTableUsage for daily logging of results
3. Use SQL Audit feature to double check and verify results
4. Retire tables in a safe way

Prerequisites

The first step is to get a ShowTableUsage procedure. The latest version can be downloaded from GitHub repository. Just deploy it (create) to any database on your server.

How to Use the ShowTableUsage Stored Procedure:

If you run the SP without specifying any parameter, it will show table usage statistics for currently selected database (in which context you are now). If you run the procedure while using the @DatabaseName parameter, it will show table usage information for the specified database. Example code:

-- Shows the tables for the current database
EXEC ShowTableUsage  
GO
-- Show tables for one database
EXEC ShowTableUsage @DatabaseName = 'Database1'  
/* or */EXEC ShowTableUsage 'Database1'

The output will look similar to this:

Note that there are far more columns to the right than is shown on the picture. For SQL Server 2019-2025, there are 37 columns in SP indicating some activities on the indexes. What ShowTableUsage stored procedure does is aggregating index usage information for each table.

Pay attention to the rightmost column Sum_Usage, which is a sum of all activities on table’s indexes or heap.

Now equipped with ShowTableUsage, you can already see what is used and what is not, based on current metadata accumulated since last server restart.

Metadata and server restarts

A very important thing to remember is that after SQL instance’s restart, metadata is reset. So it could be that some of the “unused” tables reported by ShowTableUsage, may actually be used - just not so often. For example, it could have been used a week ago, before server’s restart (if it happened). Or a month ago. Or more – some important report that pulls data once in a while.

How can we capture everything that was going on for a certain duration ?

Logging of Table Usage Stats

We will configure the logging of the ShowTableUsage procedure results.  Create a logging table using the script below:

create table ShowTableUsage_Log (
IDint identity primary key,
Date_Timesmalldatetime default getdate(),

Server_Namenvarchar(128) default @@SERVERNAME,
Server_Start_Timesmalldatetime,

[Database_name]nvarchar(128),

[Schema_name]nvarchar(128),
Table_Namenvarchar(128),

Insertsbigint,
Deletesbigint,
Updatesbigint,
Ghostsbigint,
Range_Scansbigint,
Singleton_Lookupsbigint,
User_Seeksbigint,
User_Scansbigint,
User_Lookupsbigint,
User_Updatesbigint,
System_Seeksbigint,
System_Scansbigint,
System_Lookupsbigint,
System_Updatesbigint,
Row_Locksbigint,
Page_Locksbigint,
Latch_Waitsbigint,
IO_Latch_Waitsbigint,
Tree_Latch_Waitsbigint,
Tree_IO_Latch_Waitsbigint,
Forwarded_Fetchesbigint,
LOB_Fetchesbigint,
LOB_Orphan_Createsbigint,
LOB_Orphan_Insertsbigint,
ColVal_Off_Rowbigint,
COlVal_In_Rowbigint,
Row_Overflow_Fetchesbigint,
Escalation_Attemptsbigint,
Lock_Escalationsbigint,
Page_Compression_Attemptsbigint,
Page_Compression_Successbigint,
Version_Generated_In_Rowbigint,
Version_Generated_Off_Rowbigint,
Ghost_Version_In_Rowbigint,
Ghost_Version_Off_Rowbigint,
Insert_Over_Ghost_In_Rowbigint,
Insert_Over_Ghost_Off_Rowbigint,
Sum_Usagebigint
) with (data_compression=page)/* Enterprise, Standard (since 2016 SP1) and Azure SQL support Page Compression; otherwise, just omit it */

After the logging table is created, results of the ShowTableUsage procedure can be saved to the table:

/* SQL Server 2019-2025 and Azure SQL */insert into ShowTableUsage_Log (Server_Start_Time, [Database_name], [Schema_name],Table_Name, Inserts, Deletes, Updates, Ghosts, Range_Scans, Singleton_Lookups, User_Seeks, User_Scans, User_Lookups, User_Updates, System_Seeks, System_Scans, System_Lookups, System_Updates, Row_Locks, Page_Locks, Latch_Waits, IO_Latch_Waits, Tree_Latch_Waits, Tree_IO_Latch_Waits,Forwarded_Fetches,LOB_Fetches,LOB_Orphan_Creates,LOB_Orphan_Inserts,ColVal_Off_Row,COlVal_In_Row,Row_Overflow_Fetches, Escalation_Attempts, Lock_Escalations, Page_Compression_Attempts, Page_Compression_Success, Version_Generated_In_Row, Version_Generated_Off_Row,Ghost_Version_In_Row,Ghost_Version_Off_Row,Insert_Over_Ghost_In_Row,Insert_Over_Ghost_Off_Row,Sum_Usage)

exec ShowTableUsage

/* or “exec ShowTableUsage @DatabaseName = 'Database 2'”, if your stored procedure is in Database 1, and database for which you want to save stats, is Database 2 */

Note that the above will work for SQL Server 2019-2025, and Azure SQL. If you are on SQL Server 2016-2017, there are fewer columns:

/* SQL Server 2016-2017 */insert into ShowTableUsage_Log (Server_Start_Time,[Database_Name],[Schema_Name],[Table_Name],[Inserts],[Deletes],[Updates],[Ghosts],[Range_Scans],[Singleton_Lookups],[User_Seeks],[User_Scans],[User_Lookups],[User_Updates],[System_Seeks],[System_Scans],[System_Lookups],[System_Updates],[Row_Locks],[Page_Locks],[Latch_Waits],[IO_Latch_Waits],[Tree_Latch_Waits],[Tree_IO_Latch_Waits],[Forwarded_Fetches],[LOB_Fetches],[LOB_Orphan_Creates],[LOB_Orphan_Inserts],[ColVal_Off_Row],[ColVal_In_Row],[Row_Overflow_Fetches],[Escalation_Attempts],[Lock_Escalations],[Page_Compression_Attempts],[Page_Compression_Success],[Sum_Usage])

exec ShowTableUsage

/* or “exec ShowTableUsage @DatabaseName = 'Database 2'”, if your stored procedure is in Database 1, and database for which you want to save stats, is Database 2 */

Capturing Usage Stats on a Regular Basis

Next, schedule the above script of inserting into table, using the SQL Server Agent functionality. (For Azure SQL Database, elastic jobs can be used). Once a day for our purpose should be enough. The goal will be to capture usage statistics for some extended time range, like 30-60 days, or more.

Finding Tables that Were Not Used for Extended Time

Now that you have collected usage statistics for the time range long enough, you can check which tables haven’t been used, running below script:

select
[Server_Name],
[Database_Name],
[Schema_Name],
Table_Name,
sum(Sum_Usage)[Total_Sum_Usage]
from ShowTableUsage_Log
group by [Server_Name], [Database_Name], [Schema_Name], Table_Name
having sum(Sum_Usage) = 0
order by [Server_Name], [Database_Name], [Schema_Name], Table_Name

Basically, this is it – the tables that haven’t been used for the time range that your logging table covers.

Trust, But Verify

Now you have a list of tables that do not show any records of usage during your logging time range, we can do additional checks to verify there is no activity on those tables. For this, we will use a SQL Server Audit feature. First, we need to create a server audit in the master database.

USE [master]
CREATE SERVER AUDIT Table_Usage 
TO FILE (FILEPATH = 'D:\UserData\SQL_Audit');   /* paste your path here */GO

ALTER SERVER AUDIT Table_Usage WITH (STATE = ON);

If you are on Azure SQL Managed Instance, audit will need to be created TO URL (PATH = … ), and you will need a credential to access that.

Second, create a database audit specification, and list all tables that show no usage based on results from the logging table:

/* Create the database audit specification */
USE [Database1]
CREATE DATABASE AUDIT SPECIFICATION db_audit_spec_TableUsage
FOR SERVER AUDIT Table_Usage 
ADD (SELECT, INSERT, DELETE, UPDATE, REFERENCES ON Table1 BY public),
ADD (SELECT, INSERT, DELETE, UPDATE, REFERENCES ON Table2 BY public)
/* … *//* add more tables that you want to verify usage for */WITH (STATE = ON);

Having a SQL Server Audit with database specification created on tables in question will provide additional check for whether these are used or not, and help you verify this. Just note that collecting SQL Audit logs will take some time, as well.

How to query the Audit File:

SELECT * FROM sys.fn_get_audit_file('D:\UserData\SQL_Audit\Test_Audit_C288B254-E535-496F-81C2-7F616739FB9E_0_133964015550190000.sqlaudit', NULL, NULL);  /* your file name and path will be different */

Above is for SQL Server. Note that when you create an Audit in Azure SQL Managed Instance, and writing to URL, it will create a .XEL file, not the .sqlaudit one.

Retire Tables in a Safe Way

Now that you have identified unused tables, you can start retiring them in a safe way. First, take a full backup of the database, so you can restore the tables if needed. Second, instead of instantly deleting them, the first step will be to rename them to something like “prep_for_drop_Table1”.

Conclusion

In this article, we reviewed an advanced technique which describes how to identify unused tables in a database. This technique includes using the ShowTableUsage stored procedure, logging of results, verifying with SQL Audit, and retiring tables in a safe way.