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
Sql Server Powershell 
Hello Scott, thanks for posting this! It was a big help. I noticed when I ran it that I was not receiving results for all of my SQL DBs. I spoke with my SQL guy and he modified your SQL query just a little bit and I simply dumped that into your overall script and it worked like a champ. I thought that I would share.
sys.dm_db_index_usage_stats not use all database_id
I propose to use sys.databases and left join sys.dm_db_index_usage_stats
SELECT
DB_NAME(sd.database_id) AS DatabaseName,
COALESCE(
MAX(ius.last_user_seek),
MAX(ius.last_user_scan),
MAX(ius.last_user_lookup),
MAX(ius.last_user_update),
‘1/1/1900’) AS LastAccessDate,
(SELECT create_date FROM sys.databases WHERE name = ‘tempdb’) AS LastServerRestart
FROM sys.databases sd
left join sys.dm_db_index_usage_stats ius on ius.database_id = sd.database_id
WHERE DB_NAME(sd.database_id) NOT IN(‘tempdb’, ‘master’, ‘msdb’)
GROUP BY sd.database_id
ORDER BY DatabaseName
Again, thank you for putting this out there!!
No worries. I like your query better!