Powershell Dump all tables to CSV SQL Server

I searched around for a while but could not find a script to do this. So I had to piecemilled the following script from a couple different examples. The script will loop through all the tables on the specified SQL Server database and export the data to CSV format. There will be a CSV file for every table.

# Date: 5/8/2014
# Purpose: Dump all tables from specified SQL Server database to CSV
# Notes: Change the Server, Database and extract file location as needed

$database = "DATABASE_NAME"
$tablequery = "SELECT name from sys.tables"

#Delcare Connection Variables
$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 up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet

# 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 = "C:\mssql\export\$($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

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation

One thought on “Powershell Dump all tables to CSV SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s