主页 / dba / 问题 / 318377
Accepted
JTD2021
Asked: 2022-10-19 15:34:55 +0800 CST

如何将 SQL Server 中给定数据库中的所有表导出到制表符分隔文件？

  • 772

我尝试使用 SSMS，但它一次只能导出一个文件，而且我不想创建 SSIS 包。是否可以在 PowerShell 中执行此操作？

我需要文件中每个表的标题，而不仅仅是数据。

sql-server

3 个回答

  1. Best Answer

    我用过 T-SQL。

    我不生成制表符分隔的文件，而是生成 csv 文件。

    在准备这项任务时：

    1. 以管理员身份打开 ssms
    2. 检查您的计算机 bcp.exe 命令的路径是什么 - 在我的本地计算机上是C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\- 不是您真正需要它，但我总是这样做
    3. 确保您具有完全权限，并且 sql 服务器在您要创建文件的文件夹中具有完全权限。这可以使用 powershell 实现
    4. 您必须在 sql 配置中打开一些东西，按原样保存所有设置，这样您就可以确保在完成后将它们保持原样

    ``

    USE [master] 
GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
                                    N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 
                                    N'DynamicParameters', 1 
GO 
sp_configure
sp_configure 'Ad Hoc Distributed Queries',1
sp_configure 'xp_cmdshell',1
RECONFIGURE

    1. 注意与您的 sql server实例的连接

      这就是我的服务器的调用方式：

    ``

    SELECT @@SERVERNAME
--VRINDAVANA\SQL2019
--my database is called [Radhe Shyam]
    1. 我将使用游标阅读表格

    表格：

    SELECT NAME  FROM SYS.TABLES ORDER BY NAME DESC

    在此处输入图像描述

    剧本

    go
USE [RADHE SHYAM]
go

DECLARE @bcp_cmd1 VARCHAR(1000);
DECLARE @exe_path1 VARCHAR(200) =
' cd C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\ & ';
declare @name nvarchar(150)

declare ttables CURSOR FOR
       SELECT NAME  FROM SYS.TABLES ORDER BY NAME DESC


OPEN ttables  
FETCH NEXT FROM ttables INTO @name


WHILE @@FETCH_STATUS = 0  
BEGIN  
        
        print @name
        SET @bcp_cmd1 = @exe_path1 +' BCP.EXE "SELECT * FROM [RADHE SHYAM].dbo.' 
                        + @name + '" queryout  "D:\DATA\'  
                        + @name +   '.CSV" -S VRINDAVANA\SQL2019 -w -T -t; ';

        EXEC master..xp_cmdshell @bcp_cmd1;
        FETCH NEXT FROM ttables INTO @name     

END 

CLOSE ttables  
DEALLOCATE ttables

    输出

    在此处输入图像描述

    文件：

    在此处输入图像描述

    文件里面：

    在此处输入图像描述

    现在使用带有制表符分隔符的 bcp：

    替换上面脚本中的以下行

                        + @name +   '.CSV" -S VRINDAVANA\SQL2019 -w -T -t; ';

    和

                        + @name +   '.CSV" -S VRINDAVANA\SQL2019 -w -T -t"\t" ';

    获取制表符分隔符以分隔生成文件中的列。

    这是新的制表符分隔的 csv 文件生成脚本：

    go
USE [RADHE SHYAM]
go

DECLARE @bcp_cmd1 VARCHAR(1000);
DECLARE @exe_path1 VARCHAR(200) =
' cd C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\ & ';
declare @name nvarchar(150)

declare ttables CURSOR FOR
   SELECT NAME  FROM SYS.TABLES ORDER BY NAME DESC


OPEN ttables  
FETCH NEXT FROM ttables INTO @name


WHILE @@FETCH_STATUS = 0  
BEGIN  
    
    
    print @name
    SET @bcp_cmd1 = @exe_path1 +' BCP.EXE "SELECT * FROM [RADHE SHYAM].dbo.' 
                    + @name + '" queryout  "D:\DATA\'  
                    + @name +   '.CSV" -S VRINDAVANA\SQL2019 -w -T -t"\t" ';

    EXEC master..xp_cmdshell @bcp_cmd1;
    FETCH NEXT FROM ttables INTO @name 


END 

CLOSE ttables  
DEALLOCATE ttables

    以及生成的新文件的图片：

    在此处输入图像描述

    请注意，在 excel 中打开每个表字段时，都会进入不同的 excel 列，并且在文本文件中，tab字段之间有一个空格。

    • 1

  2. 除了我的其他答案，它工作正常但不包括标题。

    这个可以。两者都使用BCP 实用程序。

    它被包装在一个游标中，该游标读取当前数据库中的所有表。

    它适用于image和uniqueidentifiers数据类型。

    我已经在msdb数据库中对其进行了测试。

    --=================================
-- script to export all tables in a database
-- using bcp
-- tab delimited columns
-- include column names in the first row
-- deals with image and uniqueidentifiers data types
-- marcello miorelli v.20221020
--=================================

declare @name nvarchar(150)
declare @tschema nvarchar(150)

declare ttables CURSOR read_only fast_forward FOR
SELECT table_schema=schema_name(schema_id), NAME  
  FROM SYS.TABLES ORDER BY NAME DESC


OPEN ttables  
FETCH NEXT FROM ttables INTO @tschema ,@name


WHILE @@FETCH_STATUS = 0  
BEGIN  


print @name


        --------------------------------------------------------------------------------
        IF object_id('tempdb..##Radhe_Shyam',N'U') IS NOT NULL
           DROP TABLE ##Radhe_Shyam

        DECLARE @table_schema nvarchar(108)=@tschema --table schema
        DECLARE @table_name varchar(108)=@name--which needs to be exported
        DECLARE @fileName varchar(1008)='RadheRadhe'


        DECLARE @bcp_cmd1 VARCHAR(1000);
        DECLARE @exe_path1 VARCHAR(200) = ' cd C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\ & ';

        DECLARE @columnHeader VARCHAR(4000)
        SELECT @columnHeader=NULL

        SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')
               + ''''+column_name +''''  
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_NAME = @table_name
           AND TABLE_SCHEMA = @table_schema

        DECLARE @ColumnList VARCHAR(max)
        SELECT @ColumnList=NULL
        SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')
               + CASE WHEN r.data_type = 'image' 
                      THEN 'CAST(CAST('+r.column_name +' AS varbinary(max))AS NVARCHAR(MAX))'
                      ELSE 'CAST('+r.column_name +' AS NVARCHAR(MAX))' 
                 END
               + r.column_name 
          FROM INFORMATION_SCHEMA.COLUMNS r
          WHERE r.TABLE_NAME = @table_name
           AND r.TABLE_SCHEMA = @table_schema

        DECLARE @tempRaw_sql nvarchar(max)
        SELECT @tempRaw_sql = N'SELECT ' 
                              + @ColumnList + N' into ##Radhe_Shyam FROM ' 
                              + @table_schema  + N'.' + @table_name 
        PRINT @tempRaw_sql
        EXECUTE sp_executesql  @tempRaw_sql

        SELECT @fileName =  @table_schema  + '_' + @table_name 

        DECLARE @raw_sql nvarchar(max)
        SELECT @raw_sql = 'SELECT  '+ @columnHeader 
                           +' UNION ALL SELECT * FROM ##Radhe_Shyam'
        PRINT @raw_SQL
        SET @bcp_cmd1 = @exe_path1 +' BCP.EXE ' + '"' 
                       + @raw_SQL + '"' 
                       + ' queryout "d:\data\'+ @fileName 
                       +'.csv" -t"\t" -T -w -S '+ @@servername

        print  @bcp_cmd1 

        EXEC xp_cmdshell @bcp_cmd1 

        --------------------------------------------------------------------------------

FETCH NEXT FROM ttables INTO  @tschema ,@name


END 

CLOSE ttables  
DEALLOCATE ttables

    我已经在几个数据库中测试了这个脚本，这些数据库中有大量的表和行。

    在此处输入图像描述

    在生成的文件中，它看起来像这样：

    在此处输入图像描述

    • 1

  3. 我有点恼火，我不知道为此内置了dbatools，但这应该可以帮到你：

    $connectionSplat = @{
    sqlInstance = "localhost"
    database = "msdb"
}

$folder = New-Item -Type Directory -Path $HOME/Desktop/TableExport -Force

Get-DbaDbTable @connectionSplat | ForEach-Object {
    $data = Invoke-DbaQuery @connectionSplat -Query "select * from [$($_.schema)].[$($_.name)]" 
    $data = $data | Select-Object * -ExcludeProperty 'RowError','RowState','Table','ItemArray','HasErrors' 

    if($data.Count -gt 0){
        $data | Export-Csv "$($folder.FullName)/$($_.schema).$($_.name).tsv" -Delimiter "`t"}
    }
}

    如果您还没有 dbatools 模块，则需要安装并导入它。如果您使用的是 PowerShell 6 或 7，我建议您-UseQuotes AsNeeded选择Export-Csv.

    • 0

相关问题