I have four production database servers to manage. They are all SQL 2005 Server. Sometimes I need to know the name, owner, size, available space of all the DBs on each server. I create this little PowerShell scripts to do that instead of opening up all the servers in SQL Server Management Studio.

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content “DBServers.txt”
$OutFile = “DBServerInfo.txt”
$Line = “Production Databases information”
$Line | out-file $OutFile
foreach ($computer in $computers)
{
$Line = “rn———————————–”
$Line | out-file $OutFile -append
$Line = “Working on “ + $computer
write-host $Line
$Server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) “$computer”
$Line = “Server :” + $computer + “ (Version: “ + $Server.Information.VersionString + “)rn———————————–”
$Line | Out-File $OutFile -append

$Server.databases | select name,owner,@{Name=”Size(MB)”;Expression={“{0,8:0.00}” -f $.size}},`
@{Name=”Available Space(MB)”;Expression={“{0,8:0.00}” -f ($.SpaceAvailable/1024)}}`
| Format-Table -auto | Out-File $OutFile -append
}

write-host “Output File Created: .” $OutFile