#
# CSV File Dumper (Sxxx)
# Tom Kober 2018-01-18
#
# Dumps all tables from specified SQL Server database to CSV
#  $tablequery = "SELECT name from sys.tables"
#
# If table query is a fixed value, like below, only table is exported
# $tablequery = "SELECT 'YOURVIEWORTABLENAME'"
#
$server = "YOUR SERVER"
$database = "YOUR DATABASE"
$tablequery = "SELECT name from sys.tables"

#Declare connection
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

#Load the tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()


# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])]"

    #Specify the output location of your dump file
    $extractFile = "D:\$($Row[0]).csv"

    $command.CommandText = $queryData
    $command.Connection = $connection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation -Delimiter ';' -Encoding OEM
}