If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
   @dbName sysname
 , @fp varchar(255)
 , @User varchar(255)
 , @Pwd varchar(255)
AS
/*
 EXEC isp_bcp_out_database
    'Northwind'
  , 'd:\Data\Northwind\'
  , 'sa'
  , ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
 SELECT  -- 'EXEC Master..xp_cmdshell ' +
--    '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
    'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
  + 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat  '
  + '-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
  + '-f'+@fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
  + ' > ' + @fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
  -- + ', no_output' AS CMD
   FROM    INFORMATION_SCHEMA.Tables
  WHERE    TABLE_TYPE = 'BASE TABLE'
      ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @CMD

WHILE @@FETCH_STATUS = 0
 BEGIN
  SELECT @CMD
  SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
  EXEC master..xp_cmdshell @CMD
  SELECT @CMD = @fp + '\bcpout.bat'
  SELECT @CMD
  insert a (s)
  exec master..xp_cmdshell @cmd


  FETCH NEXT FROM bcpout INTO @CMD
 END

CLOSE bcpout
DEALLOCATE bcpout

 select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2