export table to file with column headers (column names) using the bcp utility and SQL Server 2008

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 1

From all I know, BCP only exports the data - I don't think there's any way to make it export the header row with column names, too.

One common technique seen to solve this is to use a view over your actual data for export, which basically does a UNION ALL over two statements:

• the first statement to give back one row with the column headers
• the actual data to be export

and then use bcp on that view, instead of your underlying data table directly.

Marc

Answer 2

As well as the solution from marc_s, you can also use osql or sqlcmd

This includes headers and it can act like bcp using -Q and -o. However, they don't support format files like bcp.