Bit of misnomer, it’s still using SQL executed via the dm_db_index_usage_stats dmv to get the last user seek\scan\lookup\update column to determine the last access time, but still a good script to get a list of the last usage dates for all your databases on all your servers. Also includes the last server restart time, as the dm_db_index_usage_stats resets upon server restart. Use at your own risk.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $servers = Get-Content c:\Servers.txt $outputs = @(); $lastAccessSQL = " SELECT DB_NAME(database_id) AS DatabaseName, COALESCE( MAX(last_user_seek), MAX(last_user_scan), MAX(last_user_lookup), MAX(last_user_update), '1/1/1900') AS LastAccessDate, (SELECT create_date FROM sys.databases WHERE name = 'tempdb') AS LastServerRestart FROM sys.dm_db_index_usage_stats WHERE DB_NAME(database_id) NOT IN('tempdb', 'master', 'msdb') GROUP BY database_id ORDER BY DatabaseName " $servers | %{ $srvName = $_ $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $srvConn.ServerInstance = $srvName $dr = $srvConn.ExecuteReader($lastAccessSQL) while($dr.Read()){ $output = New-Object -TypeName PSObject -Property @{ ServerName = $srvName DatabaseName = $dr.GetString(0) LastAccessDate = $dr.GetDateTime(1) ServerLastRestart = $dr.GetDateTime(2) } $outputs += $output } } $outputs | SELECT ServerName, DatabaseName, LastAccessDate, ServerLastRestart | Out-GridView