Recently developed these queries to assist with the evaluation of development or sandbox environments.  Sometimes disk space demands that old projects be cleared out, and managers/admins want to know what has and hasn't been used recently.

These queries are built around the dm_db_index_usage_stats DMV and use the last_user_scan, last_user_seek, and last_user_update dates to determine the databases and tables that have actually been used recently.  This is done at the index level.

Someone logging into a database but reading/writing nothing would not register here, which may be a good thing.

Note that these queries are only worthwhile if the server and the databases have been in place for a while.  The dates reset when the SQL service restarts. The dates could also be reset if the database is

detached/attached, or when indexes are changed.

Enabling the AUTO_CLOSE feature will also prevent this query from being useful to you - a case statement was added to prevent this unwise setting from skewing your results.

--last user-accessed time of objects in the current database contextselect
Object = s.name + '.'

+ o.name
, o.object_id,

o.create_date, LastAccessed = CASE WHEN d.is_auto_close_on = 1 THEN 'AUTO_CLOSE is on.

Access datetimes are

unreliable'ELSE convert(varchar(30), MAX(COALESCE(last_user_scan, last_user_seek,

last_user_update)), 120 ) ENDfrom sys.dm_db_index_usage_stats

usinner

join sys.objects o on us.object_id = o.object_idinner

join sys.schemas s on o.schema_id = s.schema_idinner

join sys.databases d on d.database_id =

us.database_idwhere us.database_id =

DB_ID()and o.is_ms_shipped = 0
group

by s.name + '.' + o.name, o.object_id, o.create_date, d.is_auto_close_onorder

by LastAccessed asc, Object asc

--last

user-accessed databases on the serverselect

d.name,

Compatibility = case compatibility_level when 70 then '7.0' when 80 then '2000' when 90 then '2005' when 100 then '2008' when 110 then '2012' end, database_create_date = create_date, SQLServer_Start_Time
, LastAccessed = CASE WHEN d.is_auto_close_on = 1 THEN 'AUTO_CLOSE is on.

Access datetimes are

unreliable'ELSE convert(varchar(30), MAX(COALESCE(last_user_scan, last_user_seek,

last_user_update)), 120 ) ENDfrom sys.databases d
left

outer join sys.dm_db_index_usage_stats us
on d.database_id =

us.database_idcross

apply sys.dm_os_sys_infowhere d.database_id >

4group

by d.name,compatibility_level, create_date, SQLServer_Start_Time, d.is_auto_close_onorder

by LastAccessed asc, d.name

asc