SupportSQL

Uses xp_cmdshell (note: it is not a recommended practice to enable these procs) but is a useful tool to bcp all tables in a database (@dbname) into a directory (@filepath i.e. ‘c:\’ ).

Here’s the proc:

CREATE procedure [gp_DBA_BCPAllTablesOut]
    @dbname varchar(150) – i.e. ‘myDb’
    ,@filepath varchar(255) – i.e. ‘\some_server\some_share\some_dir' (add trailing backslash)
as

declare @c varchar(200)

set nocount on
create table #a (name varchar(128), id int identity)
set @c = ‘select table_schema + ”.” + name from ’ + @dbname +’..sysobjects o join ’ + @dbname + ‘.information_schema.tables i on i.table_name = o.name where xtype = ”U” ’
insert #a (name) 
exec (@c)

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 ’ + @dbname + ‘.’ + name + ’ out “’ + @FilePath + name + ‘.bcp” -S’ + @@servername + ’ -N -T’
    from #a where id = @id
    exec master..xp_cmdshell @cmd

end
drop table #a

—by Alek Kirstein – SupportSQL.com