I do use the following 'BLOB' of code to 'track' usage. (Edit : Sorry, tables only).
This is run repeatedly and shows the usage of tables between two calls.
Here the code is geared towards the short term 'monitoring' and shows which tables are actually used.
Taking a 'permanent' copy of the ##before table and storing this, this script can be altered to do the monitoring over a larger period. And changing what is visible will allow you to see the tables which have not been touched during the period. (Select the tables not present in ##Changed_counts)
--
-- ben brugman
-- aka: stef ten bras
-- 20160211
-- script to monitor usage in a database.
--
if object_id('tempdb..##before') is not null drop table ##before
if object_id('tempdb..##AFTER') is not null select * into ##before from ##AFTER
if object_id('tempdb..##AFTER') is not null drop table ##AFTER
-----------------------------------------------------------------------------------------------------------------
SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS
,p.rows
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,i.type_desc
,i.name
,i.index_id
,'-----' [-----]
-- ,o.*
,'------' [------]
-- ,i.*
,'-------' [-------]
into ##AFTER
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE --i.type_desc = 'HEAP' and
SCHEMA_NAME(o.schema_id) <> 'sys' and database_id = db_id()
ORDER BY p.rows desc
-- select * from ##after
exec ('if object_id(''tempdb..##before'') is null select * into ##before from ##AFTER')
--
-- Isolate all rows in the dataset which have altered.
-- Keep them in ##Changed_counts
--
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts') drop table ##Changed_counts
select * into ##Changed_counts from (
select * from ##AFTER
except
select * from ##before ) as xxx
-- select * from ##changed_counts
--
-- Show all rows which are altered
--
select
a.
as table_name
,A.NAME AS INDEX_NAME
,A.INDEX_ID
,a.user_seeks - b.user_seeks delta_seeks
,a.user_scans - b.user_scans delta_scans
,a.user_lookups - b.user_lookups delta_lookups
,a.user_updates - b.user_updates delta_updates
, a.*
from ##Changed_counts A join ##before B on a.
= b.
AND A.INDEX_ID = B.INDEX_ID
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts') drop table ##Changed_counts
I find this piece of coding usefull for several monitoring functions.
Please inform us, if this was/is usefull.
Ben