Export Delimited with Text Qualifier - Updated

 Updated version of the earlier post for exporting data to a delimited file with quoted text values. This one uses sqlcmd instead of bcp, though bcp is still included as an option. Also a bug fix in the metadata query

param(
    [Parameter(mandatory=$true)]
    [string[]]$Tables,

    [Parameter(mandatory=$true)]
    $Server,

    [Parameter(mandatory=$true)]
    $Database,

    [Parameter(mandatory=$true)]
    $OutPath ,

    [Parameter(mandatory=$false)]
    $Schema = "dbo",

    [Parameter(mandatory=$false)]
    $Separator = ",",

    [Parameter(mandatory=$false)]
    $Extension = "csv",

    [switch]$Usebcp
)

$bcp = "bcp.exe"
$sqlcmd = "sqlcmd.exe"

if(!(Test-Path -Path $outPath))
{
    throw "Output path doesn't exist"
    exit -1
}
 
$tm_start = (Get-Date) # Just used to get extract timings

foreach($t in $Tables)
{
    [string]$columnQuery = "select stuff((
                select ',' +
                    case
                     when system_type_name like '%char%' then 'QUOTENAME([' + name + '], CHAR(34))'
                     else QUOTENAME(name)
                    end
                   + ' as ' + QUOTENAME(name)
                   as [text()]
                from sys.dm_exec_describe_first_result_set ('select * from $Schema.$t', null, 0)
                for xml path (''), type).value('.[1]', 'nvarchar(max)')
                ,1, 1, '')
                as collist"
    $metadata = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $ColumnQuery
    
    $query = "select {0} from {1}.{2}" -f $metadata.collist, $Schema, $t
    #write-host $query
    $outFile = [System.IO.Path]::Combine($OutPath, $("$t.$Extension"))

    if($useBcp)
    {
        $bcpparams = "`"$query`"", "queryout", "`"$outFile`"", "-S", $Server, "-d", $Database, "-T", "-c","-t$Separator", "-C", "65001"
        #Write-Host $params
        $b = & $bcp $bcpparams
        #$b
    }
    else
    {
        $sqlparams = "-s$Separator", "-d", $Database, "-S", $Server, "-W", "-Q", $("set nocount on; $query")
        $result = & $sqlcmd $sqlparams #| findstr /v /c:"--" /b > $outFile
        $fout = [System.IO.StreamWriter]::new($outFile)
        $pattern ='[-](?!\d)(?!\w)(?! \w)'

        for($i = 0; $i -lt $result.Count; $i++)
        {
            if(!($result[$i] -match $pattern))
            {
                $fout.WriteLine($result[$i])
            }
        }

        $fout.Close()
    }
}
 
# Extract time
$tm_end = Get-Date

[timespan]$span = $tm_end - $tm_start

$span.Seconds