Thursday, July 26, 2012

Get-LocalDB

My first attempt at creating LocalDB specific PowerShell code, is a function to extract a list of LocalDB instances. This is not very exciting code, PowerShell-wise, but I wanted to share it anyways:

 Function Get-LocalDB {
    Param()
    $ldb_version = Invoke-Expression "sqllocaldb v"
    if ($ldb_version -notlike "*(11.0*)") {
        throw "Unsupported version of SQL LocalDB : $ldb_version"
    }
    [array]$ldb_instances = Invoke-Expression "sqllocaldb i"
    foreach ($ldb_instance in $ldb_instances) {
        $ldb_instanceDetails = Invoke-Expression "sqllocaldb i '$ldb_instance'"
        $obj = New-Object Object
        foreach ($line in $ldb_instanceDetails) {
            switch -wildcard ($line) {
                "Name:*"                {$obj | Add-Member Noteproperty Name -value $line.padright(21).remove(0,20)}
                "Version:*"                {$obj | Add-Member Noteproperty Version -value $line.padright(21).remove(0,20)}
                "Shared name:*"            {$obj | Add-Member Noteproperty SharedName -value $line.padright(21).remove(0,20)}
                "Owner:*"                {$obj | Add-Member Noteproperty Owner -value $line.padright(21).remove(0,20)}
                "Auto-create:*"            {$obj | Add-Member Noteproperty AutoCreate -value $line.padright(21).remove(0,20)}
                "State:*"                {$obj | Add-Member Noteproperty State -value $line.padright(21).remove(0,20)}
                "Last start time:*"        {$obj | Add-Member Noteproperty LastStartTime -value $line.padright(21).remove(0,20)}
                "Instance pipe name:*"    {$obj | Add-Member Noteproperty InstancePipeName -value $line.padright(21).remove(0,20)}
                Default                 {}
            }
        }
        $obj
    }
}

Before we rty it out, lets create a couple of extra LocalDB instances, using the sqllocaldb.exe tool:

sqllocaldb create "MySecretInstance"
sqllocaldb create "Some Things are Fishy #3"

Then start the first one:

sqllocaldb start "MySecretInstance"

Calling the function, and piping the output into a format-table -autoformat, now results in the following:

Get-LocalDB | ft -au

 Name                     Version     SharedName Owner       AutoCreate State   LastStartTime       InstancePipeName
----                     -------     ---------- -----       ---------- -----   -------------       ----------------
MySecretInstance         11.0.2318.0            Ares\Jagoop No         Running 26-07-2012 13:51:06 np:\\.\pipe\LOCALDB#66BC3610\tsql\query
Some Things are Fishy #3 11.0.2318.0            Ares\Jagoop No         Stopped 26-07-2012 13:49:21
v11.0                    11.0.2318.0            Ares\Jagoop Yes        Running 26-07-2012 11:22:11 np:\\.\pipe\LOCALDB#822B23E5\tsql\query




No comments: