How can I export all of the Tables from a given Database in SQL Server to tab separated files?

Question

I tried using SSMS but it only lets me export one file at a time and I'd prefer not to create an SSIS package. Is it possible to do this in PowerShell?

I need the headers of each table in the file as well not just the data.

Answer 1

I have used T-SQL.

I don't generate tab delimited files, but csv files.

In preparation to this task:

1. open ssms as administrator
2. check in your computer what is the path to the bcp.exe command - on my local machine it is C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\ - not that you actually need it but I always do it this way
3. make sure that you have full permissions and the sql server has full permissions in the folder that you are going to create the files. this you can achieve using powershell
4. you would have to open a few things in the sql configuration, save all the settings as they are, so that you can make sure you leave them as they were after your finish

``

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

5. pay attention to the connectivity to your sql server instance

   this is how my server is called:

``

SELECT @@SERVERNAME
--VRINDAVANA\SQL2019
--my database is called [Radhe Shyam]

6. I am going to use a cursor to read through the tables

the tables:

SELECT NAME  FROM SYS.TABLES ORDER BY NAME DESC

The script

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 

The output

The files:

Inside the files:

Now using bcp with tab delimiter:

Replacing the following line in the script above

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

WITH

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

Gets a tab delimiter to separate the columns in the generated file.

Here is the new tab delimited csv file generation script:

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 

And a picture of the new files generated:

Note that when opening in excel each table field goes into a different excel column and in the text file, there is a tab space between the fields.

Answer 2

Further to my other answer which works fine but does not include the headers.

this one does. Both use the BCP utility.

It is wrapped in a cursor that reads all the tables in the current database.

It works for image and uniqueidentifiers data types.

I have tested it in the msdb database.

--=================================
-- 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 

I have tested this script in several databases with a good number of tables and rows in those tables.

Inside of the generated files it looks like this:

Answer 3

I'm a bit annoyed I don't know of a dbatools builtin for this but this should do you in a pinch:

$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"}
    }
}

You need to install and import the dbatools module if you don't already have it. And if you're on PowerShell 6 or 7, I recommend the option -UseQuotes AsNeeded for Export-Csv.