SQL Server Management Studio 2012 - 将数据库的所有表导出为 csv [英] SQL Server Management Studio 2012 - Export all tables of database as csv

问题描述

我在 SQL Server 中有一个包含很多表的数据库，并希望以 csv 格式导出所有表.来自之前问过的一个非常相似的问题 - 从 SQL Server 导出2012 年通过 Management Studio 转换为 .CSV

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.

我想要的是一次导出所有表的能力.SQL Server 导入和导出向导一次只允许一个表.如果您有一个非常大的数据库，这将非常麻烦.我认为更简单的解决方案可能涉及编写查询，但不确定.

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.

推荐答案

导出向导一次只允许一个.我使用 powershell 脚本将所有表导出到 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
}

谢谢

这篇关于SQL Server Management Studio 2012 - 将数据库的所有表导出为 csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！