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
