2

I want to query SQL Server in PowerShell using Invoke-DbaSqlCmd.

Invoke-DbaSqlCmd -SqlInstance DBA -Query 'exec xp_fixeddrives'

This will return a System.Data.DataRow object. The object type is not really important. The important thing to know is the result will be different depending on SQL Server language. In French it will be 'Mo disponible' and English will be 'MB Free'. I want my code to work in any SQL Server language available.

enter image description here

I want to select the second column without using Get-Member. Like the solution here:

$RST = Invoke-DbaSqlCmd -SqlInstance DBA -Query 'exec xp_fixeddrives'
$SecondCol = ($RST | Get-Member -MemberType Properties)[1].Name
$RST.$SecondCol

Is there an elegant way of returning the second column without using it's name?

PollusB
  • 1,726
  • 2
  • 22
  • 31
  • Can someone unmark this question as duplicate? The associated question can't be a duplicate because it refrers to lines. I'm refering to columns. A collection will have multiple objects and these objects have properties. Object would be Line and Propertie would be Column. – PollusB Aug 13 '20 at 16:06

1 Answers1

3

No, this is the "elegant" way to do it.

For performance reasons, you may want to inspect only the first object returned to $RST rather than piping every single row to Get-Member:

$RST = Invoke-DbaSqlCmd -SqlInstance DBA -Query 'exec xp_fixeddrives'
$SecondCol = ($RST[0].psobject.Properties |Select -Index 1).Name
$RST.$SecondCol
Mathias R. Jessen
  • 157,619
  • 12
  • 148
  • 206