home
bcp data from all tables in database
Author Nigel Rivett


This will execute a bcp on all tables in the database

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)
begin
	select @id = min(id) from #a where id > @id
	select @cmd = 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -S' + @@servername + ' -N -T'
	from #a where id = @id
	exec master..xp_cmdshell @cmd
	select @cmd
end
drop table #a


This will give a resultset of the bcp commands which you can copy to a query window and execute.

	select 'exec master..xp_cmdshell ''bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -S' + @@servername + ' -N -T'''
	from sysobjects where xtype = 'U'


This will give a resultset of the bcp commands which you can copy to a .bat file and run.

	select 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -S' + @@servername + ' -N -T'
	from sysobjects where xtype = 'U'


For bcp in change out to in.



For identity columns

	select 'exec master..xp_cmdshell ''bcp ' + db_name() + '..' + o.name + ' in "c:\bcp\' + o.name + '.bcp" -S' + @@servername + ' -N -T'
		+ case when c.id is null then '' else ' -E' end + ''''
	from sysobjects o 
		left join (select distinct id from syscolumns where status & 0x80 <> 0) c
			on o.id = c.id
	where o.xtype = 'U'




home