Create Procedure sp_DBABCPOut  	@db varchar(10),		--  Table Name
			 	@srv varchar(10),		--  Server Name
				@path varchar(10) = 'C:\BCP\'	--  Output Directory
					
AS	
	      
/*******************************************************************************************
  * Procedure:  sp_DBABCPOut
  *    
  *   Purpose:  BCP ALL user data from a specified database and 
  *  		 specified server to given location
  *
  *    Inputs:   Database Name, Server Name, Output Path (Optional)
  *   Assumes:  There exists a directory called  C:\BCP, if no output directory is given
  *
  *    Output:   3 files per table :- <database>_<table>.dat  ( Data file )
  *				      <database>_<table>.out  ( BCP Statical info )
  *				      <database>_<table>.err  ( Error File )
  *
  * --------------------------------------------------------------------------------------------
  */

declare @tbl varchar(255), 			
	@next_tbl varchar(255),			
	@cmd Varchar(1000)




exec ('declare d cursor for SELECT name FROM ' + @db + '..sysobjects WHERE type = ' + '''u''')

open d
fetch next from d into @tbl
SET NOCOUNT  ON


while (@@FETCH_STATUS<>-1) begin
	
	select @cmd = 	'bcp  '+ @db+ '..' + @tbl + ' out '  +  @path + @db +'_'+ @tbl +'.dat'  +
			' /e ' + @path + @db +'_'+ @tbl + '.err'  + ' /n /E /o ' + @path + @db +'_'+ @tbl +'.out'  +
			' /T  /S ' + @srv

	execute master..xp_cmdshell @cmd 
	Print 'BCP the ' + @tbl + ' Table'

	fetch next from d into @tbl
	
End
close d
deallocate d