List databases health with SQL SMO

 

"Get-Databases" function creates a custom "view" of some database properties on the specified SQL server.

 

function Get-SQLDatabases{
    param(
        [string]$server=$(throw "Please specify SQL server name.")
    )

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $smo = new-object Microsoft.SqlServer.Management.Smo.Server $server
    $count=0;

    $smo.databases | foreach {
        $db = $smo.databases[$_.name];
        $obj = new-object psobject;
        add-member -inp $obj NoteProperty "#" ($count+1);
        add-member -inp $obj NoteProperty "Server" ([string][regex]::replace($db.Parent,"\[|\]","")).toupper();
        add-member -inp $obj NoteProperty "DB Name" $db.Name;
        add-member -inp $obj NoteProperty "ID" $db.id;
        add-member -inp $obj NoteProperty "Size(MB)" $db.size.tostring("N2");
        add-member -inp $obj NoteProperty "Free(MB)" ($db.SpaceAvailable/1024).tostring("N2") ;
        add-member -inp $obj NoteProperty "Status" $db.Status;
        add-member -inp $obj NoteProperty "Last Backup" $db.LastBackupDate;
        add-member -inp $obj NoteProperty "SystemDB" $db.IsSystemObject;
        $obj;
        $count++
    }
}

 

PS > Get-SQLDatabases shayl\sqlexpress | format-table -autosize

# Server                     DB Name ID Size(MB) Free(MB) Status  Last Backup               SystemDB
- ------                       -------      -- --------    --------    ------    -----------                   --------
1 SHAYL\SQLEXPRESS master    1  6.75       1.44        Normal  01/01/0001 00:00:00  True
2 SHAYL\SQLEXPRESS model     3  3.19       1.08        Normal  01/01/0001 00:00:00  True
3 SHAYL\SQLEXPRESS msdb      4  9.44       2.00        Normal  01/01/0001 00:00:00  True
4 SHAYL\SQLEXPRESS tempdb   2  2.69      1.03         Normal  01/01/0001 00:00:00  True
5 SHAYL\SQLEXPRESS test        5  7.13       0.20        Normal  01/01/0001 00:00:00  False