How do I Export All Tables in a Selected Database to Tab-Delimited Text Files with Field Names Using BCP?
Posted on 2006-06-01
What I want to do is automate the export of all tables in a particular database using BCP.
The export format must be tab-delimited ASCII text with fieldnames as the first row, and I have to have a separate text file for each table.
I have adapted the following code (hats off to Nigel Rivett), which does everything except insert the field names as the first row:
set nocount on
create table #a (name varchar(128), id int identity)
insert #a (name) select name from sysobjects where xtype = 'U'
declare @id int, @cmd varchar(2000)
select @id = 0
while @id < (select max(id) from #a)
select @id = min(id) from #a where id > @id
select @cmd = 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.txt" -S' + @@servername + ' -c -T'
from #a where id = @id
exec master..xp_cmdshell @cmd
drop table #a
My question is twofold:
Given my goals, do you see anything wrong with the BCP arguments above?
Is it possible to get the field names as the first row too?