Export data from All SQL tables within a Database in CSV format- Using Powershell


Here is the script you can use to export all the data from SQL tables within a given database into csv format. This script is purely powershell based.

Import-Module sqlps

$SQLServer = "localhost" 
$DatabaseName = "DatabaseName"
$ExportLocation = "Path to Export"

$Tables = (Get-SqlDatabase -ServerInstance $SQLServer -Name  $DatabaseName).tables
foreach($table in $Tables)    {
        $SQLquery="select * from $($table)"
        $result=invoke-sqlcmd -query $SQLquery -serverinstance $SQLServer -database $DatabaseName 
        Write-Host "Now Exporting Table $table"
        $result |export-csv "$ExportLocation$($table.Name).csv" -notypeinformation