invoke-sqlcmd use column names within the script

PowerShell Help
1

[SOLVED]I use invoke-sqlcmd extensively and am building automation that takes SQL statements from a config file, executes them and produces various email alerts.

In the script, I need to dynamically reference the column names so I can plug various fields into the body of the message. Using invoke-sqlcmd, how can I load those column names into a variable (array or hashtable) so that I can reference them within the script?

[MY SOLUTION]

Basically, you can take the results of invoke-sqlcmd and get the column names using the following:

$ColumnsHeaders = @()

$Results = invoke-sqlcmd -server -query “select * from table…”

$ColumnCount = $Results[0].itemarray.count

For ($Ctr = 0; $Ctr -lt $ColumnCount; $Ctr++) {

$ColumnHeaders += $Results[0].table.columns[$Ctr].caption

}

2 
$ColumnNames = @('Green','Blue','Yellow')

#region some script here

'Referencing the first column - the 0 element of the $ColumnNames array'
$ColumnNames[0]

#endregion
3

I appreciate the response, but your code assumes that we know the column names in advance. I actually figured out how to do it and will post my solution shortly.

4

This is possible to do dynamically as originally requested:

PS C:\Users\rasim> 
$sqlResult = @()
$sqlResult += [pscustomobject]@{FirstName = 'John';LastName='Smith';Hobby='Curling'}
$sqlResult += [pscustomobject]@{FirstName = 'Sally';LastName='Johnson';Hobby='Monopoly'}

$sqlResult


FirstName LastName Hobby   
--------- -------- -----   
John      Smith    Curling 
Sally     Johnson  Monopoly


#Take first row and get the PSOBject property names
PS C:\Users\rasim> $sqlResult[0].PSObject.Properties.Name
FirstName
LastName
Hobby