将所有SQL Server表立即导出到txt或csv。

I have hundreds of SQL Server tables to export to txt or csv with " text qualifier and | delimited. Import/Export wizard allows only one table at a time.

我有数百个SQL Server表要导出到txt或csv，其中包含“文本限定符和|分隔符”。导入/导出向导一次只允许一个表。

Is there any better method/tool/script to this all at once?

是否有更好的方法/工具/脚本同时进行?

Thanks

谢谢

3 个解决方案

#1

You could do something with BCP using the following:

你可以用BCP做以下事情:

SELECT 'bcp [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] out "' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt" -T -c -t"|" -d {DATABASE NAME} -S {SERVER NAME} -T'
FROM INFORMATION_SCHEMA.TABLES

This will output BCP statements for each of the tables in the database you run it against. You could then copy and paste those into a command window.

这将为运行它的数据库中的每个表输出BCP语句。然后可以将它们复制并粘贴到命令窗口中。

In the above, you'd want to replace {DATABASE NAME} and {SERVER NAME} with the details for your environment. The "-T" uses a trusted connection (i.e., you) so if you need to do something with a specific username and password, you'd need to adjust accordingly. Take a look at the BCP util write-up for more details.

在上面，您希望将{数据库名称}和{服务器名称}替换为您的环境的详细信息。“-T”使用可信连接(即。因此，如果需要使用特定的用户名和密码进行操作，则需要相应地进行调整。更多细节请看BCP util的报道。

The SELECT may require more tweaking based on the names of the objects in your DB but hopefully this gives you some idea of where/how to start.

选择可能需要根据数据库中对象的名称进行更多的调整，但希望这能让您对从何处/如何开始有所了解。

#2

I saw your post and looked at the Export tool in SQL management studio too, noticed it doesnt do the multi table export you're talking about (in my case the sql management studio 2016 preview). I thought perhaps I'd write a quick PowerShell script to do the job.

我看到了您的文章，也查看了SQL management studio中的Export工具，注意到它没有执行您正在讨论的多表导出(在我的例子中是SQL management studio 2016 preview)。我想也许我应该写一个PowerShell脚本来完成这项工作。

This does the job for me at least, 1. extracts a list of all the tables in the database, then 2. loops through all the table and 3. selects from the tables and exports to csv in the predefined location.

这至少对我有帮助，1。提取数据库中所有表的列表，然后是2。循环遍历所有表和3。从表中选择并导出到预定义位置的csv。

I executed via PowerShell ISE on windows 10, powershell version 5.

我在windows 10上执行了PowerShell ISE, PowerShell版本5。

If you're unsure of your version run:

如果你不确定你的版本运行:

$PSVersionTable.PSVersion

Just remember to change your execution policy before running it e.g. https://technet.microsoft.com/en-us/library/ee176961.aspx

在运行执行策略之前，请记住更改执行策略，例如https://technet.microsoft.com/en-us/library/ee176961.aspx

I made mine unrestricted before executing this script.

在执行这个脚本之前，我不受限制。

Set-ExecutionPolicy Unrestricted

Here's the script I wrote.

这是我写的剧本。

$databaseName="<DATABASE_NAME>"
$instanceName="<HOSTNAME\INSTANCENAME>"
$baseExportPath="C:\temp1\dbexport"
$query = "SELECT name FROM sys.Tables"

$tableNames = Invoke-SqlCmd –ServerInstance $instanceName -Database $databaseName –Query $query

New-Item -Force $baseExportPath -type directory

foreach($dataRow in $tableNames)
{
   $exportFileName=$baseExportPath + "\\" + $dataRow.get_Item(0).ToString() + ".csv"

   $tableSpecificQuery="select * from " + $dataRow.get_Item(0).ToString()
   Invoke-SqlCmd –ServerInstance $instanceName -Database $databaseName –Query $tableSpecificQuery | Export-Csv -Path $exportFileName -NoTypeInformation
}

I didn't explicitly specify a delimiter for the export-csv function but it could be easily done by adding

我没有明确地为export-csv函数指定分隔符，但是可以通过添加来轻松实现

-Delimiter '|'

#3

The Import/Export wizard creates and runs a bare-bones SSIS package. Use multiple data flow tasks in a package to do additional tables. A good start would be saving the package from the wizard and copy/pasting the task there. From there, change your data source and destination in each task.

导入/导出向导创建并运行一个简单的SSIS包。在包中使用多个数据流任务来执行其他表。一个好的开始是将包从向导中保存，并在那里复制/粘贴任务。然后，在每个任务中更改数据源和目标。

Working in BIDS or SQL Data Tools isn't too bad for something simple like this.

对于像这样简单的东西来说，在投标或SQL数据工具中工作并不是坏事。

https://msdn.microsoft.com/en-us/library/ms141122.aspx

#1