MSSQL: bcp command to dump individual tables in a database into csv format with table header

 Dear Readers,

I had a requirement where I need to take a dump of all the tables in couple of SQL Server database into a csv files. Remember it is 1 csv per table.

Code to use:

DECLARE @tablcatlg VARCHAR(50) -- database name
DECLARE @tablschm VARCHAR(50) -- schema name
DECLARE @tablnm VARCHAR(50) -- table name
DECLARE @tablefulnm varchar(150) -- table full name
DECLARE db_cursor CURSOR FOR 
SELECT trim(TABLE_CATALOG), trim(TABLE_SCHEMA), trim(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tablcatlg,@tablschm,@tablnm
WHILE @@FETCH_STATUS = 0  
BEGIN  
select 'set BCP_EXPORT_DB='+@tablcatlg
select 'set BCP_SCHEMA_NAME='+@tablschm
select 'set BCP_JUST_TABL='+@tablnm
select 'BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +'+''''+','+''''+', '+''''+''''+') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='+''''+'%BCP_SCHEMA_NAME%'+''''+' and TABLE_NAME='+''''+'%BCP_JUST_TABL%'+''''+'; select @colnames;" queryout HeadersOnly.csv -c -T -S'
select 'BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S'
select 'copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv'
select 'set BCP_JUST_TABL='
select 'set BCP_EXPORT_DB='
select 'set BCP_SCHEMA_NAME='
select 'del HeadersOnly.csv'
select 'del TableDataWithoutHeaders.csv'
select ' '
      FETCH NEXT FROM db_cursor INTO @tablcatlg,@tablschm,@tablnm
END 
CLOSE db_cursor  
DEALLOCATE db_cursor

The above code should be run from sqlcmd with -h -1 flag & "set nocount on".Like below..

sqlcmd -s <servername> -h -1

> set nocount on;

Example usage:

-- Set db to attempt

1> use landonhotel;

2> go

Changed database context to 'landonhotel'.

1>

1> DECLARE @tablcatlg VARCHAR(50) -- database name

2> DECLARE @tablschm VARCHAR(50) -- schema name

3> DECLARE @tablnm VARCHAR(50) -- table name

4> DECLARE @tablefulnm varchar(150) -- table full name

5>

6> DECLARE db_cursor CURSOR FOR

7> SELECT trim(TABLE_CATALOG), trim(TABLE_SCHEMA), trim(TABLE_NAME)

8> FROM INFORMATION_SCHEMA.TABLES

9> WHERE TABLE_TYPE = 'BASE TABLE'

10>

11> OPEN db_cursor

12> FETCH NEXT FROM db_cursor INTO @tablcatlg,@tablschm,@tablnm

13> WHILE @@FETCH_STATUS = 0

14> BEGIN

15> select 'set BCP_EXPORT_DB='+@tablcatlg

16> select 'set BCP_SCHEMA_NAME='+@tablschm

17> select 'set BCP_JUST_TABL='+@tablnm

18> select 'BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +'+''''+','+''''+', '+''''+''''+') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='+''''+'%BCP_SCHEMA_NAME%'+''''+' and TABLE_NAME='+''''+'%BCP_JUST_TABL%'+''''+'; select @colnames;" queryout HeadersOnly.csv -c -T -S'

19> select 'BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S'

20> select 'copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv'

21> select 'set BCP_JUST_TABL='

22> select 'set BCP_EXPORT_DB='

23> select 'set BCP_SCHEMA_NAME='

24> select 'del HeadersOnly.csv'

25> select 'del TableDataWithoutHeaders.csv'

26> select ' '

27>

28>       FETCH NEXT FROM db_cursor INTO @tablcatlg,@tablschm,@tablnm

29> END

30>

31> CLOSE db_cursor

32> DEALLOCATE db_cursor

33> go

set BCP_EXPORT_DB=landonhotel

set BCP_SCHEMA_NAME=dbo

set BCP_JUST_TABL=Guests

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='%BCP_SCHEMA_NAME%' and TABLE_NAME='%BCP_JUST_TABL%'; select @colnames;" queryout HeadersOnly.csv -c -T -S

BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv

set BCP_JUST_TABL=

set BCP_EXPORT_DB=

set BCP_SCHEMA_NAME=

del HeadersOnly.csv

del TableDataWithoutHeaders.csv

set BCP_EXPORT_DB=landonhotel

set BCP_SCHEMA_NAME=humanresource

set BCP_JUST_TABL=Employees

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='%BCP_SCHEMA_NAME%' and TABLE_NAME='%BCP_JUST_TABL%'; select @colnames;" queryout HeadersOnly.csv -c -T -S

BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv

set BCP_JUST_TABL=

set BCP_EXPORT_DB=

set BCP_SCHEMA_NAME=

del HeadersOnly.csv

del TableDataWithoutHeaders.csv

set BCP_EXPORT_DB=landonhotel

set BCP_SCHEMA_NAME=dbo

set BCP_JUST_TABL=rooms

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='%BCP_SCHEMA_NAME%' and TABLE_NAME='%BCP_JUST_TABL%' order by ordinal_position; select @colnames;" queryout HeadersOnly.csv -c -T -S

BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv

set BCP_JUST_TABL=

set BCP_EXPORT_DB=

set BCP_SCHEMA_NAME=

del HeadersOnly.csv

del TableDataWithoutHeaders.csv

set BCP_EXPORT_DB=landonhotel

set BCP_SCHEMA_NAME=dbo

set BCP_JUST_TABL=RoomReservations

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='%BCP_SCHEMA_NAME%' and TABLE_NAME='%BCP_JUST_TABL%'; select @colnames;" queryout HeadersOnly.csv -c -T -S

BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv

set BCP_JUST_TABL=

set BCP_EXPORT_DB=

set BCP_SCHEMA_NAME=

del HeadersOnly.csv

del TableDataWithoutHeaders.csv

1>

Put the output above into a .bat file and you can run the same from cmd prompt.

Remember not to run this code in parallel, since the staging file name are same. So you will end up messing the output.

Also please go to the directory where you want to dump the csv file(I am sure you will validate the disk space ;))

The example output of the .bat script run below:

C:\vagrant\bcp>set BCP_EXPORT_DB=landonhotel

C:\vagrant\bcp>set BCP_SCHEMA_NAME=dbo

C:\vagrant\bcp>set BCP_JUST_TABL=Guests

C:\vagrant\bcp>BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='%BCP_SCHEMA_NAME%' and TABLE_NAME='%BCP_JUST_TABL%'; select @colnames;" queryout HeadersOnly.csv -c -T -S

Starting copy...

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 1      Average : (1000.00 rows per sec.)

C:\vagrant\bcp>BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S

Starting copy...

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC Driver 17 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.

8 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 1      Average : (8000.00 rows per sec.)

C:\vagrant\bcp>copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv

HeadersOnly.csv

TableDataWithoutHeaders.csv

        1 file(s) copied.

C:\vagrant\bcp>

...

C:\vagrant\bcp>set BCP_EXPORT_DB=landonhotel

C:\vagrant\bcp>set BCP_SCHEMA_NAME=dbo

C:\vagrant\bcp>set BCP_JUST_TABL=RoomReservations

C:\vagrant\bcp>BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='%BCP_SCHEMA_NAME%' and TABLE_NAME='%BCP_JUST_TABL%'; select @colnames;" queryout HeadersOnly.csv -c -T -S

Starting copy...

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 47     Average : (21.28 rows per sec.)

C:\vagrant\bcp>BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S

Starting copy...

2 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 1      Average : (2000.00 rows per sec.)

C:\vagrant\bcp>copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv

HeadersOnly.csv

TableDataWithoutHeaders.csv

        1 file(s) copied.

C:\vagrant\bcp>set BCP_JUST_TABL=

C:\vagrant\bcp>set BCP_EXPORT_DB=

C:\vagrant\bcp>set BCP_SCHEMA_NAME=

C:\vagrant\bcp>del HeadersOnly.csv

C:\vagrant\bcp>del TableDataWithoutHeaders.csv

C:\vagrant\bcp>

In excel:

The advantage of this code is, it will print the column header as well :)

Thank you!

Appendix 1)

In case you face an error like "SQLState = S1000, NativeError = 0

Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server"

Refer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ba623ee-8b48-44ce-ab1b-c6bf5af5a2e3/hostfile-columns-may-be-skipped-only-when-copying-into-the-server?forum=transactsql

The original blog was written for SQL server 2019; there the proc worked just fine. But in older version of SQL Server say 2005 or so it failed with the error mentioned.

Probably use the below code instead for such situation...

DECLARE @tablcatlg VARCHAR(50) -- database name

DECLARE @tablschm VARCHAR(50) -- schema name

DECLARE @tablnm VARCHAR(50) -- table name

DECLARE @tablefulnm varchar(150) -- table full name

DECLARE db_cursor CURSOR FOR 

SELECT trim(TABLE_CATALOG), trim(TABLE_SCHEMA), trim(TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @tablcatlg,@tablschm,@tablnm

WHILE @@FETCH_STATUS = 0  

BEGIN  

select 'set BCP_EXPORT_DB='+@tablcatlg

select 'set BCP_SCHEMA_NAME='+@tablschm

select 'set BCP_JUST_TABL='+@tablnm

select 'BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames +'+''''+','+''''+', '+''''+''''+') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='+''''+'%BCP_SCHEMA_NAME%'+''''+' and TABLE_NAME='+''''+'%BCP_JUST_TABL%'+''''+' order by ordinal_position; set fmtonly off select @colnames;" queryout HeadersOnly.csv -c -T -S'

select 'BCP %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL% out TableDataWithoutHeaders.csv -c -t, -T -S'

select 'copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv %BCP_EXPORT_DB%.%BCP_SCHEMA_NAME%.%BCP_JUST_TABL%.csv'

select 'set BCP_JUST_TABL='

select 'set BCP_EXPORT_DB='

select 'set BCP_SCHEMA_NAME='

select 'del HeadersOnly.csv'

select 'del TableDataWithoutHeaders.csv'

select ' '

      FETCH NEXT FROM db_cursor INTO @tablcatlg,@tablschm,@tablnm

END 

CLOSE db_cursor  

DEALLOCATE db_cursor

Thank you!