Question: I have seen a number of hacks to try to get the bcp utility to export column names along with the data. If all I am doing is dumping a table to a text file what is the most straightforward method to have bcp add the column headers?

Here's the bcp command I am currently using:

```
bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T

```

Answer: 
This method automatically outputs column names with your row data using [BCP](https://msdn.microsoft.com/en-au/library/ms162802.aspx).

The script writes one file for the column headers (read from `INFORMATION_SCHEMA.COLUMNS` table) then appends another file with the table data.

The final output is combined into `TableData.csv` which has the headers and row data. Just replace the environment variables at the top to specify the Server, Database and Table name.

```
set BCP_EXPORT_SERVER=put_my_server_name_here
set BCP_EXPORT_DB=put_my_db_name_here
set BCP_EXPORT_TABLE=put_my_table_name_here

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

BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER%

set BCP_EXPORT_SERVER=
set BCP_EXPORT_DB=
set BCP_EXPORT_TABLE=

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

```

Note that if you need to supply credentials, replace the -T option with -U my\_username -P my\_password

This method has the advantage of always having the column names in sync with the table by using `INFORMATION_SCHEMA.COLUMNS`. The downside is that it creates temporary files. Microsoft should really fix the bcp utility to support this.

This solution uses the SQL row concatenation trick from [here](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) combined with bcp ideas from [here](http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/)
Some of the solutions here are overly complex. Here's one with 4 lines of code, no batch files, no external apps and all self-contained in the SQL server.

In this example, my table is named "MyTable" and it has two columns named Column1 and Column2. Column2 is an integer, so we need to CAST it to varchar for the export:

```
DECLARE @FileName varchar(100)
DECLARE @BCPCommand varchar(8000)
DECLARE @ColumnHeader varchar(8000)

    SET @FileName = 'C:\Temp\OutputFile.csv'
 SELECT @ColumnHeader = COALESCE(@ColumnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'
    SET @BCPCommand = 'bcp "SELECT '+ @ColumnHeader +' UNION ALL SELECT Column1, CAST(Column2 AS varchar(100)) AS Column2 FROM MyTable" queryout "' + @FileName + '" -c -t , -r \n  -S . -T'
   EXEC master..xp_cmdshell @BCPCommand

```

You could add this to a stored procedure to fully automate your .CSV file (with header row) creation.