BCP all user tables of a database

-- BCP out all user tables
-- Author       G. Hanson
-- Date Added   02/23/2005
-- Last Changed mm/dd/yyyy
--
-- Syntaxusp_BCP_out_AllTables 'database','path for datafiles','sql server name'
--
-- Notesall data output files are tablenameyyyymmddhhss.dat
-- Change History

CREATE PROCEDURE usp_BCP_out_AllTables
@dbname varchar(30),
@pathvarchar(50) = "C:\Temp",
             @servervarchar(50) = "sql server name"
AS

SET NOCOUNT ON

DECLARE @tablename varchar(30)
DECLARE @cmdlinevarchar(255)
DECLARE @ssql                   varchar(255)
DECLARE @tabcountsmallint
DECLARE @todaychar(14)

set @today =
 substring(convert(char(20),getdate(),20),1,4)+
       substring(convert(char(20),getdate(),20),6,2)+
       substring(convert(char(20),getdate(),20),9,2)+
       substring(convert(char(20),getdate(),20),12,2)+
       substring(convert(char(20),getdate(),20),15,2)+
       substring(convert(char(20),getdate(),20),18,2) 

SELECT @tabcount = 0 

EXEC ('USE ' + @dbname)

create table #dumptables ([name] varchar(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname + '..sysobjects where type = ''U''
                   and name <>  "dtproperties"'
exec (@ssql)

DECLARE cnames CURSOR FOR 
select [name] from #dumptables

OPEN cnames

FETCH NEXT FROM cnames INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM cnames INTO @tablename
CONTINUE
END

        
PRINT 'Exporting table: ' + @tablename

/* build commandline */-- Add "-S<servername>" for a remoteserver, terminator used = ~ (tilde), specify terminator after '-t', '-T' is used for trusted connection, 
-- use -U<username> -P<password> for standard security
SELECT @cmdline = 'bcp ' + @dbname + '..' + @tablename + ' out ' + @path + '\' + @tablename + @today +'.dat -c  -t  -T -S' + @server
print @server
print @cmdline
EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT

SELECT @tabcount = @tabcount + 1
FETCH NEXT FROM cnames INTO @tablename
END

DEALLOCATE cnames

/* Print usermessage */SELECT CONVERT(varchar(10),@tabcount) + ' tables from database '+ @dbname + ' exported to ' + @path
GO