BCP all user tables of a database
This script will BCP all user tables of a database to individual files. The file names are tablenameyyyymmddhhmmss.dat
To execute
usp_bcp_out_alltables 'database','path','server'
You will need to change your default
1) path
2) server
-- BCP out all user tables
-- Author G. Hanson
-- Date Added 02/23/2005
-- Last Changed mm/dd/yyyy
--
-- Syntax usp_BCP_out_AllTables 'database','path for datafiles','sql server name'
--
-- Notes all data output files are tablenameyyyymmddhhss.dat
-- Change History
CREATE PROCEDURE usp_BCP_out_AllTables
@dbname varchar(30),
@path varchar(50) = "C:\Temp",
@server varchar(50) = "sql server name"
AS
SET NOCOUNT ON
DECLARE @tablename varchar(30)
DECLARE @cmdline varchar(255)
DECLARE @ssql varchar(255)
DECLARE @tabcount smallint
DECLARE @today char(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