Mapping unused tables in SQL Server is a common task whenever willing to do some environment clean-up.
This task might sound quite simple (and it is!) but, one should pay attention that any information gathered from well-known DMVs will be relevant only from the last SQL Server instance restart!
What if we wish to gather such information over weeks, or months?
This script will guide you into a quick setup for creating 2 tables and a stored procedure. The latter is then to be executed manually or in a scheduled job:
Here's a summary of the steps:
All the objects below can be created in any database of your choice but, I recommend creating a dedicated database (our example uses a database called IndexUsageStatsDB). Don't forget to enter the right value for @MyDatabase variable!!!
create the [dbo].[IndexUsageStats] table:
create the [dbo].[IndexUsageStatsSnap] table:
create the [dbo].[MergeIndexUsageStats] stored procedure:
All you'll have to do is scheduling an Agent job for executing the
[dbo].[MergeIndexUsageStats] stored procedure on a regular basis. After it has been gathering information for long enough, you'll be able to analyze the data found in [dbo].[IndexUsageStats] table and understand i.e. which tables have not been accessed for a given period of time.
Enjoy!!!