SQL Server Management Studio 2012 - Export All Tables of Database as CSV

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

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

Write query to export database to csv file via Microsoft SQL Server Management Studio

Try this:

:!!sqlcmd -S sqlbusServer -d hhhdb -E -Q "SELECT * FROM Table" 
-o "FILEPATH\MyData.csv" -h-1 -s"," -w 700

I simply added :!! front of "sqlcmd" and a filepath in front of the document name.

Examples:
http://www.codeproject.com/Tips/1017732/SQL-Server-export-to-CSV

Import / Export database with SQL Server Server Management Studio

Right click the database itself, Tasks -> Generate Scripts...

Then follow the wizard.

For SSMS2008+, if you want to also export the data, on the "Set Scripting Options" step, select the "Advanced" button and change "Types of data to script" from "Schema Only" to "Data Only" or "Schema and Data".

Automated Export to CSV Using SQL Server Management Studio

What you need is to schedule a job to run every week. Please have a look at here http://msdn.microsoft.com/en-us/library/ms191439.aspx

---

Related Topics