Powershell Invoke-Sqlcmd Output To Array

We are looking at the result set for invoke-sqlcmd which normally returns an array of objects. We can re-assign the value to an array or deal with the object and their methods using a different notation. To identify the result set “object” you can execute the following powershell.

$resultobject = Invoke-Sqlcmd -ServerInstance 'SQLServer01' -Query 'SELECT database_id, [name], recovery_model_desc FROM sys.databases' -Database "master" 

$resultobject.GetType()  ## BaseType is System.Array of Object[]'s
$resultobject.name
$resultobject.recovery_model_desc

Notice using dot notation we can access any of the SQL query results stored in the [array] –> PSObject and deal with the single value or pass the object to a “format-table” or out-gridview”.
Another option is to access the array element and the object stored there directly.
Add the following to the above code to access each value using it’s array index.

$resultobject[0].name
$resultobject[0].recovery_model_desc
$resultobject[1].name
$resultobject[1].recovery_model_desc

Convert to HashTable

Maybe we would rather use a named index, a key and a value for that key?
We can use a foreach and hash table to assign the results to a new hash table. Using the same Invoke-sqlcmd command add the following code to create a new Hash Table and populate it with the DB name as the key and the Recovery Model for the value.

$hashtbl = @{}  ## new hash table

foreach($r in $resultobject) {
    $hashtbl.Add($r.name, $r.recovery_model_desc)
}
$hashtbl.Keys
$hashtbl.Values

$hashtbl.ContainsKey('msdb')  ## $True
$hashtbl.ContainsValue('SIMPLE')  ## $True
$hashtbl.ContainsValue('simple')  ## $False, case sensitive

$hashtbl.GetEnumerator().ForEach({"The Recovery for DB '$($_.Key)' is: $($_.Value)"})

Hashtable With Multiple Columns

Let’s change the scenario and add the other column “database_id” to our results in the hash table. What we can do is add an array with multiple values (columns) to the “value” of each hash table entry. The structure looks something like this with the “name” as our “key” and the array is the “value” of the hash table.

$hashtbl = @{'key' = 'name'; 'value' = @('database_id','recovery_model_desc')}

We can use a foreach and “Add” the columns from the query to the “value” array inside our Hash Table.

$hashtbl = @{}  ## new hash table

foreach($r in $resultobject) {
    $hashtbl.Add($r.name, @($r.database_id, $r.recovery_model_desc))
}
$hashtbl

We now have the Database name as the Key and the value contains an array with the additional columns (database_id and recovery_model).

---