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: 
For:

* Windows, 64 bit
* SQL Server (tested with [SQL Server 2017](https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server#SQL_Server_2017) and it should work for all versions):

Option 1: Command Prompt
------------------------

```
sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"

```

Where:

* `[DATABASE].[dbo].[TABLENAME]` is table to write.
* `c:\dirname\file.csv` is file to write to (surrounded in quotes to handle a path with spaces).
* Output .csv file includes headers.

Note: I tend to avoid `bcp`: it is legacy, it predates `sqlcmd` by a decade, and it never seems to work without causing a whole raft of headaches.

Option 2: Within SQL Script
---------------------------

```
-- Export table [DATABASE].[dbo].[TABLENAME] to .csv file c:\dirname\file.csv
exec master..xp_cmdshell 'sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"'

```

Troubleshoooting: must [enable xp\_cmdshell](https://stackoverflow.com/questions/5131491/enable-xp-cmdshell-sql-server) within MSSQL.

Sample Output
-------------

File: `file.csv`:

```
ID,Name,Height
1,Bob,192
2,Jane,184
3,Harry,186

```

Speed
-----

As fast as theoretically possible: same speed as `bcp`, and many times faster than manually exporting from SSMS.

Parameter Explanation (optional - can ignore)
---------------------------------------------

In `sqlcmd`:

* `-s,` puts a comma between each column.

* `-W` eliminates padding either side of the values.
* `set nocount on` eliminates a garbage line at the end of the query.

For `findstr`:

* All this does is remove the second line underline underneath the header, e.g. `--- ----- ---- ---- ----- --`.
* `/v /c:"-"` matches any line that starts with "-".
* `/b` returns all other lines.

Importing into other programs
-----------------------------

In Excel:

* Can directly open the file in Excel.

In Python:

```
import pandas as pd
df_raw = pd.read_csv("c:\dirname\file.csv")

```
Here is a pretty simple stored procedure that does the trick as well...

```
    CREATE PROCEDURE GetBCPTable
    @table_name varchar(200)
AS
BEGIN
    DECLARE @raw_sql nvarchar(3000)

    DECLARE @columnHeader VARCHAR(8000)
    SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

    DECLARE @ColumnList VARCHAR(8000)
    SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +' AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

    SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM ' + @table_name
    --PRINT @raw_SQL
    EXECUTE sp_executesql  @raw_sql
END
GO

```