PowerShell Array to Table like output -- Possible ForEach problem

Question

I'm trying to run a sql query via PowerShell and return the results in a table-like format.

It's putting multiple results in one field. I suspect there's something wrong with the 'foreach' loops. What am I missing, please?

To use the code below, just change the server names from 'server1'/'server2' for your sql server instances.

$query = "
 SELECT   @@SERVERNAME     AS ServerName       
                   , (SELECT DB_NAME ())              AS DBName
                   , s.name                                        AS SchemaName
                   , st.name                                       AS TableName
                   , RIGHT(st.name, 8)                    AS Rgt8
                   , TRY_CONVERT(DATE, RIGHT(st.name, 8), 103) AS Rgt8Date
                   
                   FROM        sys.tables  AS st
                   INNER JOIN  sys.objects AS so ON so.object_id = st.object_id
                   INNER JOIN  sys.schemas AS s ON s.schema_id = st.schema_id
                   "
$instanceNameList = @('server1', 'server2')
$report = @()
 
foreach ($instanceName in $instanceNameList) {
    write-host "Executing query against SERVER/INSTANCE: " $instanceName        
    $dbNames = Invoke-DbaQuery -SqlInstance $InstanceName -Database "master" -Query "select name from sys.databases where database_id > 4 and name <> 'TEST'"          

    foreach ($database in $dbNames.Name ) {
        Write-host -Activity "Current DATABASE $database" -Status "Querying: $database"                               
        $results = Invoke-DbaQuery -SqlInstance $InstanceName -Database $database -Query $query 

        # <#          
        if ($results -is [array]) {
            $CustomObject = [pscustomobject]  @{
                ServerName           = $results.ServerName
                DBName                 = $results.DBName
                SchemaName        = $results.SchemaName
                TableName             = $results.TableName
                Rgt8                         = $results.Rgt8
                Rgt8Date                 = $results.Rgt8Date
                OverOneYearOld   = $results.OverOneYearOld         
                Drop_Table             = $results.Drop_Table                           
            }
        ## ADDING EACH ROW/JOB OBJECT THAT HAS BEEN REPORTED, TO THE REPORT ARRAY       
        $report += $CustomObject      
        }        
    } 
}
          
$report | Select-Object ServerName, DbName, TableName | Out-GridView

if `$results -is [array]` then you should loop over it :) you should also be able to use `Select-Object` — Santiago Squarzon, Feb 04 '22 at 20:20

score 1 · Accepted Answer · answered Feb 04 '22 at 20:38

Basically, you're doing the opposite of what you wanted to do, if $results -is [array] you want to iterate over it instead of add it as is to to your $report array.

On the other hand, adding elements to a fixed collection (+=) is a terrible idea.

$dbquery = @'
select name from
sys.databases
where database_id > 4 and name <> 'TEST'"
'@

$result = foreach ($instanceName in $instanceNameList) {
    $params = @{
        SqlInstance = $InstanceName
        Database = "master"
        Query = $dbquery
    }
    $dbNames = Invoke-DbaQuery @params

    foreach ($database in $dbNames.Name) {
        $params.Database = $database
        $params.Query = $query
        Invoke-DbaQuery @params | Select-Object @(
            'ServerName'
            'DBName'
            'SchemaName'
            'TableName'
            'Rgt8'
            'Rgt8Date'
            'OverOneYearOld'
            'Drop_Table'
        )
    }
}

$result | Format-Table

@JM1 my pleasure, happy to help. looking back at my code, if those are the exact properties you're pulling from your DB, the `Select-Object` statement might not be even needed at all — Santiago Squarzon, Feb 07 '22 at 20:12

PowerShell Array to Table like output -- Possible ForEach problem

1 Answers1