Exporting all tables into files from SQL Server database using utility bulk copy

Question

I want to bcp all tables into files from a database:

SELECT 'EXEC xp_cmdshell ''bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               --database name
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- schema
+  QUOTENAME(name)                         -- table
+ ' out c:\temp\'                          -- output directory
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+  REPLACE(name,' ','')                    -- file name
+ '.txt -T -c'''   -- extension, security
FROM sys.tables

it produces statements like this:

EXEC xp_cmdshell 'bcp [AdventureWorks2012].[Production].[ScrapReason] out c:\temp\Production_ScrapReason.txt -T -c'

EXEC xp_cmdshell 'bcp [AdventureWorks2012].[HumanResources].[Shift] out c:\temp\HumanResources_Shift.txt -T -c'

so what I want is to iterate over above statements and execute all them. How to do this?

Matt Tucker · Accepted Answer · 2014-01-09 14:33:32Z

4

Note this assumes all commands are unique.

DECLARE @Commands TABLE(CommandText NVARCHAR(4000));
DECLARE @SQL NVARCHAR(4000);

INSERT INTO @Commands
SELECT 'EXEC xp_cmdshell ''bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               --database name
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- schema
+  QUOTENAME(name)                         -- table
+ ' out c:\temp\'                          -- output directory
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+  REPLACE(name,' ','')                    -- file name
+ '.txt -T -c'''   -- extension, security
FROM sys.tables

WHILE (SELECT COUNT(*) FROM @Commands) > 0
BEGIN --Command Processing
    SET @SQL = (SELECT TOP 1 CommandText FROM @Commands)
    --PRINT (@SQL)
    EXEC (@SQL)
    DELETE FROM @Commands WHERE CommandText = @SQL
END

edited Jan 9, 2014 at 14:33

Matt Tucker

1855 bronze badges

answered Nov 12, 2013 at 16:42

David

1,6111 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Willy Over a year ago

I have not tried yet because I had not time but the backup (c:\temp\...) done where is IT placed? in my local machine or in the remote machine?

David Over a year ago

On the server you run the script on. To get it elsewhere us a unc path.

Tim Abell Over a year ago

Watch out for Server blocked access to procedure 'sys.xp_cmdshell' which has to be enabled to be able run the above sql. I avoided this by altering the sql to print the bare bcp commands and creating a .bat file from that.

Timo Riikonen · Accepted Answer · 2020-07-30 09:24:03Z

Default setting is that you can't execute bcp command from SQL server. This version works from DOS, Powershell and BAT file.

I didn't find setting from BCP which would export the heading line on these CSV files, so note that these CSV files don't include heading.

-- Run this command to the database you wish to export
DECLARE @Commands TABLE(CommandText NVARCHAR(4000));
DECLARE @SQL NVARCHAR(4000);

INSERT INTO @Commands
SELECT 'bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               --database name
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- schema
+  QUOTENAME(name)                         -- table
+ ' out c:\temp\csvdump\'                  -- output directory
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+  REPLACE(name,' ','')                    -- file name
+ '.csv -T -C 65001 -t "|" -k -c -S localhost'   -- extension, security
FROM sys.tables

-- Copy-paste results to DOS or Powershell prompt or create a BAT file from these.
SELECT 'mkdir c:\temp\csvdump'
UNION ALL
SELECT * FROM @Commands

Collectives™ on Stack Overflow

Exporting all tables into files from SQL Server database using utility bulk copy

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related