If the database has lot many unwanted tables which are not being used by any of the applications and if you want to identify the ununused objects , so that you can remove or move those objects to a separate database , then the system view sys.dm_db_index_usage_stats (sql server 2008) contains the information with when the table accessed last time.
the script SQL-1 will produce the list of the tables which are being used and non-used as well. the records in the column [recent_datetime] contains the list of tables which are non been used since the server started.
Note : the counters on this DMV starts / refresh when the server is restarted.
-- Query-1
with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id = b.object_id
)
select tbl_name,max(access_datetime) as recent_datetime from cte_recent
group by tbl_name
order by recent_datetime desc , 1
To keep a track of the tables which are non been used , its better to store the results in a separate table and merge the table before restart the sql server or as per your own schedule as the SQL Server installed on cluster then you probably schedule it as a job to wait for an year before making the decision to remove or move those tables to a different database.
create a table TABLES_LAST_ACCESSED with 2 columns as below script
--Query-2
create table TABLES_LAST_ACCESSED (TBL_NAME VARCHAR(256),recent_datetime DATETIME)
sync the table with the script (Query-3) below
-- Query-3
with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id = b.object_id
)
MERGE TABLES_LAST_ACCESSED AS target
USING (select tbl_name,max(access_datetime) as recent_datetime from cte_recent group by tbl_name) AS source
ON (Target.tbl_name = Source.tbl_name)
when matched then
UPDATE SET Target.recent_datetime = Source.recent_datetime
when not matched then
INSERT (tbl_name,recent_datetime)VALUES (source.tbl_name,source.recent_datetime);
query any table by using select , insert , update or delete and execute the below statement. the tables which you accessed recently should be in the top.
SELECT * FROM TABLES_LAST_ACCESSED ORDER BY 2 DESC
