Technical Article

Script to generate BCP Statements for a Database

,

The Script below creates a Stored Procedure that

returns a Macro SQL Statement. When this SQL statement is executed, it inturn returns BCP Out Statements for all the

tables in a given database. The database name should be passed as a parameter to the procedure.

Before the procedure is created, the SA Password must be changed to your Database SA Password, for the BCP statements to work correctly.

IF object_id('sp_bcp_scripts') >99999
drop procedure sp_bcp_scripts 
go
create procedure sp_bcp_scripts (@dbname varchar(50))AS
BEGIN
/* The Script below creates a Stored Procedure that 
   returns a Macro SQL Statement which when executed 
   will inturn return BCP Out Statements of all the 
   tables in a given database passed as a parameter
   Author : Seshu Kanuri, DBA, EB2B Commerce, Inc.
*/
declare @sqlstr varchar(4000)
select @sqlstr="select 'bcp '+" +"'"+ @dbname +"..'"+ '+name+'+  "' OUT prod_Db/DataIn/'"+'+name+'+ "'.dat -c -e prod_Db/DataOut/' " +   '+name+'+" '.err -S Prod1 -U sa -P oasis -t ¢' from sysobjects where type='U' order by name "
select @sqlstr
/* To run the script - Execute sp_bcp_scripts Northwind */end
go

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating