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.