SQL Server Management Studio 2012 - 将数据库的所有表导出为 csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30791482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Management Studio 2012 - Export all tables of database as csv
提问by Ashwini Khare
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
我在 SQL Server 中有一个包含很多表的数据库，并希望以 csv 格式导出所有表。来自之前提出的一个非常相似的问题 -通过 Management Studio 从 SQL Server 2012 导出到 .CSV
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.
我想要的是一次导出所有表的能力。SQL Server 导入和导出向导一次只允许一个表。如果您有一个非常大的数据库，这将非常麻烦。我认为更简单的解决方案可能涉及编写查询，但不确定。
回答by sree
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.
导出向导一次只允许一个。我使用 powershell 脚本将所有表导出到 csv 中。如果对你有帮助，请试试这个。
$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
谢谢
回答by JamieD77
Instead of clicking Export Data, choose Generate Scripts. Select the tables you want, click next and click the Advancedbutton. The last option under Generalis Types of data to script. Chose Schema and dataor just Data.
不要单击Export Data，而是选择Generate Scripts。选择您想要的表，单击下一步并单击Advanced按钮。下面的最后一个选项General是Types of data to script。选择Schema and data或只是Data。
回答by NP3
The answer by sree is great. For my db, because there are multiple schemas, I changed this:
sree 的回答很棒。对于我的数据库，因为有多个模式，我改变了这个：
$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"
回答by w5ar
Comment on @annem-srinivas solution: If you use schemas other than the default (dbo), change the following in his script:
评论@annem-srinivas 解决方案：如果您使用默认（dbo）以外的模式，请在他的脚本中更改以下内容：
$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"
回答by JerryGoyal
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
1）首先为所有表创建一个脚本：
Tasks->Generate Scripts->所有表->单个sql文件
2) Create a dummy database and run this script.
2) 创建一个虚拟数据库并运行此脚本。
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
3）右键单击虚拟数据库并选择任务->导出数据->选择源数据->选择目标为Microsoft Excel并给出其路径->执行
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.
复制此结果并将其粘贴到 CSV 中。
