It often happens that DBA’s/Developers end up creating database objects(mostly tables) during development phase for various reasons like intermediate testing or backup. Such objects often end up on production databases.
DBA’s/Developers often repeat same things on production databases as well
- Backup before performing quick fix
- Performance optimization
It is always a good practice to delete such objects created for temporary purposes. However, most of the times DBA’s/Developers forget to drop them.
- Unorganized database
- Unnecessary space claim
How if there was a way to identify tables/indexes that are not used? Well, not to worry there is a way to find out tables and indexes that are not used.
Here is the script (using onf of the DMV’s) to find out the tables/indexes that are not used by any SQL process.
Such objects can be dropped after consulting with team members supporting the particular application.
P:S – The statistics are reset with SQL restart.
SELECT OBJECT_NAME(i.[object_id]) AS [Table name] , CASE WHEN i.name IS NULL THEN '<Unused table>' ELSE i.name END AS [Index name] FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE 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() ) AND o.[type] = 'U' ORDER BY OBJECT_NAME(i.[object_id]) ASC;