I needed to export a number of tables from a SQL server to text files today. BCP is great but doesn’t include header information which I needed. With some help from a couple of internet posts I wrote this batch file which worked great. It will take all the tables from a database (edit batch file to specify the database name) and export them to separate pipe delimited files. You can change the delimiter by replacing the two ^| with , if you want comma-delimited. It will also pause after creating the list of tables and open a notepad of all of the tables allowing you to remove any tables that don’t need to be exported (in my case they had a few tables containing binary attachments). Existing files are NOT overwritten allowing you to restart if needed.

set dbname=[insert database name here]
rem correct drive and path for your application
d:
md d:export
cd d:export
BCP "select name from %dbname%.dbo.sysobjects where xtype='U' order by name" queryout tablelist.txt -c -T
cls
echo if needed, edit the tablelist.txt file now before continuing
notepad tablelist.txt
pause
rename tablelist.txt tablelist.tmp
for /F "tokens=*" %%a in (tablelist.tmp) do call :Sub %%a
goto :EOF
:Sub
if exist %1.txt goto skip
BCP "select column_name from %dbname%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%1' order by ordinal_position" queryout header.tmp -c -r^| -T
BCP %dbname%.dbo.%1 out data.tmp -c -t^| -T
type header.tmp >headercrlf.tmp
echo. >>headercrlf.tmp
copy /b headercrlf.tmp+data.tmp %1.txt
erase data.tmp
erase header.tmp
erase headercrlf.tmp
erase tablelist.tmp
:skip