SQL Server Management Studio 2012 - Export all tables of database as csv

Question

I have a database in SQL Server with a lot of tables and wish to export all tables in csv format. From a very similar question asked previously - Export from SQL Server 2012 to .CSV through Management Studio

Right click on your database in management studio and choose Tasks -> Export Data...

Follow a wizard, and in destination part choose 'Flat File Destination'. Type your file name and choose your options.

What I want is the capability to export all tables at once. The SQL Server Import and Export Wizard only permits one table at a time. This is pretty cumbersome, if you have a very big database. I think a simpler solution might involve writing a query, but not sure.

Solution 1

The export wizard allows only one at a time. I used the powershell script to export all my tables into csv. Please try this if it helps you.

$server = "SERVERNAME\INSTANCE"
$database = "DATABASE_NAME"
$tablequery = "SELECT schemas.name as schemaName, tables.name as tableName from sys.tables inner join sys.schemas ON tables.schema_id = schemas.schema_id"

#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
$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])].[$($Row[1])]"

    #Specify the output location of your dump file
    $extractFile = "C:\mssql\export\$($Row[0])_$($Row[1]).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
}

Thanks

Solution 2

Instead of clicking Export Data, choose Generate Scripts. Select the tables you want, click next and click the Advanced button. The last option under General is Types of data to script. Chose Schema and data or just Data.

Solution 3

The answer by sree is great. For my db, because there are multiple schemas, I changed this:

$tablequery = "SELECT schemas.name as schemaName, tables.name as tableName from sys.tables inner join sys.schemas ON tables.schema_id = schemas.schema_id"

and then also

$queryData = "SELECT * FROM [$($Row[0])].[$($Row[1])]"

#Specify the output location of your dump file
$extractFile = "C:\mssql\export\$($Row[0])_$($Row[1]).csv"

Solution 4

Comment on @annem-srinivas solution: If you use schemas other than the default (dbo), change the following in his script:

$tablequery = "SELECT schemas.name as schemaName, tables.name AS tableName from sys.tables INNER JOIN sys.schemas ON schemas.schema_id
= tables.schema_id ORDER BY schemas.name, tables.name"

and

$queryData = "SELECT * FROM [$($Row[0])].[$($Row[1])]"
$extractFile = "C:\temp\export\$($Row[0]).$($Row[1]).csv"

View more solutions

Comments

[Ashwini Khare]

Even if I select "Data" only in last option, the exported files are in Microsoft SQL Server Query File format. It's not text, or csv, which is what I want. Any workaround for that?

[bDreadz]

Thank you for this. Had some trouble with the SERVERNAME\INSTANCE --- What worked me was just to have the SERVERNAME there and then making sure I had the folders c:\mssql\export folders created as well. Cheers.

[tonyyeb]

Is it easy enough to append the table name in each of the exported files with the number of rows in the table? I'm trying to use this for comparative purposes and that would make things a lot easier for me. Great script by the way!

[Furqan Hameedi]

One more improvement in this script can be addition of "-encoding "unicode" " parameter on Export-Csv line to support international languages.

[KookieMonster]

A very useful script. One caveat though... It will only work well for not too big tables. After a while, memory will get saturated, I guess mostly by the last line. If anyone has a more memory safe version of this script, that could be very useful.

[Douglas Gaskell]

Unfortunately the Excel export has never actually worked for me. There is ALWAYS a massive slew of errors from too many rows (seems to cap at 2^16 when excel can hold 2^20) to conversion errors.

[EAmez]

In SS2016 I can't find this option. Generate Scripts only lets me choose database objects but doesn't give option to choose data from tables.

[JamieD77]

@EAmez I can still see the option. After you choose options, the Set Scription Options tab has an Advanced button for more options. Click that and you should see Types of data to script as the last option under General. It defaults to Schema only but you can choose Data only or Schema and data.

[EAmez]

@JamieD77, you were right. I had missed the button "Advanced" in that step of scripting database. Thank you.

[Giffyguy]

This is extremely useful. Most databases have multiple schemas, and it's important to account for this in the script.

[user1805743]

with MSSQL Studio 18 I keep getting .sql files, both when selecting 'Data only' or 'Schema and Date' :/ how can i fix this? I need the csv's

[jrdevdba]

Thank you for this script! It proved helpful for me for a database that had to be "downgraded" - backed up on SQL 2016 but the other person has only SQL 2014. Thanks again.

[Tan Rezaei]

Can someone please tell me how this script was able to get permission to run the necessary queries? It worked for me, but it never asked for a DB user or Password. Is it using my windows credentials? If so how can I have it use a specific user's credentials?

[Simon Mourier]

Thanks! If you want to make sure it can be opened directly by Excel just double-clicking on it, in column mode, with support for any language characters, add this to the Export-Csv command: -Encoding "unicode" -Delimiter "`t" (equivalent to this UI stackoverflow.com/a/36018426/403671)

[w. Patrick Gale]

This answer is not relevant to exporting to CSV.

[JamieD77]

@w.PatrickGale 21 people found it useful, so I left it. Thank you for your input though.