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
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.
Leave a Reply