How to process multiple columns from sql in powershell

Question

I have this PowerShell script:

$QUERY = "SELECT name FROM sys.databases";    
$Databases = invoke-sqlcmd -serverinstance "SQLInstanceName" -database "master" -Query $QUERY

foreach ($dbname in $Databases)
{
    $dbname
}

Let's say I want the recovery model for the databases as below, how do I get them into PowerShell variables?

$QUERY = "SELECT name, recovery_model_desc FROM sys.databases";

Could you post the results of $Query | Get-Member? With that information it should be possible to get what you want via some code. — RetiredGeek, Jul 01 '20 at 19:52

score 0 · Answer 1 · answered Jul 01 '20 at 22:57

May be you are looking for

$QUERY = "SELECT * from sys.databases";    
$Databases = invoke-sqlcmd -serverinstance "SQLInstanceName" -database "master" -Query $QUERY

# This is just to print the individual value of column (name and recovery_model)
$Databases | ForEach {
    Write-Host '----------------------------------------'
    Write-Host 'Database :' $_.name
    Write-Host 'Recovery model :' $_.recovery_model
    Write-Host '----------------------------------------'
    Write-Host
}

# This will give you name and recovery_model of database
$Databases | Select name, recovery_model

How to process multiple columns from sql in powershell

1 Answers1