ExportToCSV.ps1

# Database servername
$Servers = @('server1', 'server2')
# Datbase name
$DatabaseName = "Dbname" 
# database select query
$sqlQuery = $("select top 5 * from sys.objects")
# Temporary file name with path
$csvPath = "$env:TEMP\"

 
 function QueryDatabase ($Server, $Database, $SQLQuery) {
    Write-Warning "Processing...."
    $Datatable = New-Object System.Data.DataTable
    
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    try 
    {
        $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
        $Connection.Open()
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $command.CommandTimeout=60
        $Command.Connection = $Connection
        $Command.CommandText = $SQLQuery

        $DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $Command
        $Dataset = new-object System.Data.Dataset
        $DataAdapter.Fill($Dataset) >$null| Out-Null
        $Connection.Close()
    
        $Dataset.Tables[0] | export-csv (Join-Path $csvPath "$Server.csv") -notypeinformation -Encoding UTF8
        return $Dataset.Tables[0]
    }catch{ Write-Warning $_ }    
    finally
    {
       $Connection.Dispose()
    }
}

Foreach ($server in $Servers)
{
    $dataTable = QueryDatabase $Servers $DatabaseName $sqlQuery
    $dataTable | Format-Table      
    
    # Test the results
    $P = Import-Csv -Path (Join-Path $csvPath "$Server.csv")
    $P | Format-Table #| Get-Member      
}