SQL Server Management Studio 2012 - Export all tab

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.

Answer 1

1st Way

You can replicate the schema to a temp database and then use that database to export all tables (along with column names).

Steps:

1) First, create a script for all the tables:
Tasks->Generate Scripts->all tables->single sql file

2) Create a dummy database and run this script.

3) right click on the dummy database and select tasks->export data-> select source data->select destination as Microsoft Excel and give its path->Execute

You'll get a single spreadsheet with all the tables and its columns.

2nd Way

Execute below query, it'll give all table names in 1st column along with corresponding column names in 2nd column of result.

select t.name ,c.name from sys.columns c
inner join sys.tables t
on t.object_id = c.object_id
order by t.name

Copy this result and paste it in CSV.

Answer 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.

Answer 3

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"

Answer 4

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

Answer 5

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"