sql server script to know when the table accessed last time

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