How to Identify Unused Tables In SQL Server

Introduction

When working on large-scale projects, we might have created many tables, or later some point we might have changed the structure as part of the requirements. There is a huge possibility of tables that are not required or unused for a long time. A good practice is to remove/delete those by identifying them. In this simple article, I would like to share a T-SQL statement that will retrieve the tables which are not used.

T-SQL Statement to Identify Unused Tables

The below T-SQL Statement results in the tables that are not used

SELECT 
		TN.name AS TableName,
		PS.row_count AS TotalRowCount,
		TN.create_date AS CreatedDate,
		TN.modify_date AS LastModifiedDate
FROM sys.all_objects AS TN 
JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=TN.name
WHERE TN.type ='U' 
     AND NOT EXISTS (	SELECT 
							OBJECT_ID  
						FROM sys.dm_db_index_usage_stats
						WHERE OBJECT_ID = TN.object_id
					)
ORDER BY TotalRowCount 

The results of the above T-SQL Statement is as,

Note: Before making a deletion or dropping the tables, Please make sure those are not required

Conclusion

In this simple Tips and Tricks article, we have seen how to find unused tables. I assume this tip was useful and We will learn more tips in our upcoming articles. Please feel free to share your feedback in the comment section.