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

Rate

Share

Share

Rate