How to Export All Tables in a Sql Database to CSV Files

Tagged: PowerShell

In this article we will look at how you can use PowerShell to export all tables in a database to a csv file. Csv files are really useful when it comes to working with databases, from working on the data in excel or importing into reports or migrating to different sql engines or products.

Problem#

You need csv files from your sql database, but your not sure how to export them, there are many ways, but if like me you use PowerShell for everything, then perhaps you want a PowerShell solution to export your csv files from sql server?

Solution#

PowerShell gives us access to the .NET classes, so we can use System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataAdapter and System.Data.DataSet to connect to sql and export tables to a csv.

We will start simple, and export the data for 1 table from sql server, and then export all tables.

Example 1: Export One Table to a CSV File Using PowerShell#

These examples use the StackOverFlow2010 database, in this first example we are making a connection to sql server, selecting everything from the VoteTypes table and exporting it to a csv file with the same name.

The example uses SqlCommand and it has 13 lines of code, for 1 table. In the next example we will process for all tables with less lines of code.

$Server = "localhost\MSSQLSERVER01"  
$Database = "StackOverFlow2010"

$SqlQuery = "SELECT name from $Database.dbo.VoteTypes;"  
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  
$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; Integrated Security = True;"  
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
$SqlCmd.CommandText = $SqlQuery  
$SqlCmd.Connection = $SqlConnection  
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
$SqlAdapter.SelectCommand = $SqlCmd   

$DataSet = New-Object System.Data.DataSet  
$SqlAdapter.Fill($DataSet)  
$DataSet.Tables[0] | Out-File "D:\stacko\VoteTypes.csv"

Example 2: Export all the tables from a sql server database to a csv file#

So, previous example is quite basic, we have to specify the table name, but what if we want all tables, what if we don’t know the table names or there are 100s of tables?

That would take a long time to step through manually, so lets grab the table names and loop through each one, and to make it even simpler we will use Invoke-SqlCmd instead. We have all the tables this time, and we used 1 less line of code!

$databaseName="StackOverFlow2010"
$instanceName="localhost\MSSQLSERVER01"
$baseExportPath="d:\stacko\"
$query = "SELECT name FROM sys.Tables"
$tableNames = Invoke-SqlCmd –ServerInstance $instanceName -Database $databaseName –Query $query

New-Item -Force $baseExportPath -type directory

foreach($dataRow in $tableNames)
{ 
    $exportFileName=$baseExportPath + "\\" + $dataRow.get_Item(0).ToString() + ".csv"
   $tableSpecificQuery="select * from " + $dataRow.get_Item(0).ToString()
   Invoke-SqlCmd –ServerInstance $instanceName -Database $databaseName –Query $tableSpecificQuery | Export-Csv -Path $exportFileName -NoTypeInformation
}

Conclusion#

So as you can see exporting sql tables as csv files from sql server using PowerShell is pretty simple and as usual with powershell there is more than 1 way to do it.

As you can see, using Invoke-SqlCmd saves us a lot of code as it does a lot of the work of setting up the connection for us in the background.

The latest versions of ssms also have some powerful options to quickly export data, so if you are using ssms with sql server it is worth exploring those options too, perhaps we can cover them in another article.