INF: How to Bulk Copy Out All the Tables in a Database

This article was previously published under Q176818
This article has been archived. It is offered "as is" and will no longer be updated.
This article provides a script that uses the bulk copy program (bcp) tobulk copy all your user-defined tables on any given database. This scriptwas designed to accommodate both integrated and standard security. You canalso choose to specify either native or character mode bcp. The procedurein this article is coded to use character mode, but you can simply changeone line to set the mode to native, if you want.
The script below dynamically codes a SELECT statement, using the parameterssupplied to it, and builds a batch file containing the bcp instructions toget the data from your tables. You need to run this batch file, which willactually execute the bcp commands. Also note that you may not get theexpected results if you are using extended characters. For more informationon extended characters, see the following article in the MicrosoftKnowledge Base:
153449 : INF: SQL Server Code Pages and AutoAnsiToOem Behavior

After you compile this stored procedure, you can run it from the commandprompt with the following command (note that the command should all betyped on one line):
isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\' "
-oc:\bcp\bcpscript.bat /h-1 /n

The following table describes the parameters in the above command:
   Parameter                              Description   ------------------------------------------------------------------------   isql -E                                Starts a trusted connection in                                           ISQL.   /dpubs                                 The database name where you want                                          to store this procedure.   -Q                                     Executes the query and                                         immediately exits ISQL.   "sp_bcpTablesOut 'pubs', 'c:\bcp\' "   This is the command to be                                          executed by ISQL. *   -oc:\bcp\bcpscript.bat                 The batch file generated by the                                          stored procedure. **   /h-1                                   Suppresses headings. ***   /n                                     Suppresses the prompt (>) and                                          numbering. ***				

   *   The stored procedure in this example (sp_bcpTablesOut) is passed two       parameters: the database that you want to bulk copy the tables out       to ('pubs'), and the directory where you want to place the tables       that will be bulk copied out to ('c:\bcp\'). Also note that there is       a backslash after the subdirectory name. You could also pass two       other parameters to specify LOGINID and PASSWORD.   **  You have to run the Bcpscript.bat script to actually bulk copy the       tables. To capture the results of this script into a file, run it       with the following command:          c:\bcp\bcpscript.bat >c:\bcp\results.txt   *** By default, ISQL gives you headings for each result set. Because you       are creating a script, you want to suppress the headings because       they cannot be executed. ISQL also gives you the prompt (>) and       numbers the lines. Again, you are creating a script, so you only       want code that can be executed.				

The following is an example of how to run this procedure in integratedmode (note that the command should all be typed on one line):
isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\' "
-oc:\bcp\bcpscript.bat /h-1 /n

The following is an example of how to run this procedure in standard mode(note that the command should all be typed on one line):
isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\', 'SA' "
-oc:\bcp\bcpscript.bat /h-1 /n

The following is an example of how to run the batch file and pipe resultsto a text file:
c:\bcp\bcpscript.bat >c:\bcp\results.txt

Source of the Stored Procedure

The following is the source of the stored procedure. Copy and paste it inyour database, and then compile it:
   if exists (select * from sysobjects where id =   object_id('dbo.sp_bcpTablesOut') and          sysstat & 0xf = 4) drop procedure dbo.sp_bcpTablesOut   GO   /*    dbname = database name      dirname = destination directory for bcp output      username = optional      pwd = optional   */    CREATE PROCEDURE sp_bcpTablesOut  @dbname  varchar(40),                               @dirname varchar(20),                          @username varchar(30) = NULL,                           @pwd varchar(30) = NULL                                        AS   set nocount on -- removes the rows affected count   /*   @Q1       represents single quote        @Q2       represents double quotes      @security       represents security for BCP      @myquery    represents file bcp batch output   */    declare @Q1 char(1)   declare @Q2 char(1)   declare @security varchar(255)   declare @myquery varchar(255)   declare @bcpmode char(3)   select @Q1 = "'"   select @Q2 = '"'   select @bcpmode = '-c '    -- character type   --select @bcpmode = '-n '  -- native type   /*    checks for standard or integrated security  */    IF @username IS NULL          select @security = '-T '   ELSE          select @security = '-U' + @username + ' -P' + @pwd   /*    formats final bcp output text that will be part of the script   */    select @myquery = 'SELECT ' + @Q2 + 'bcp ' + @dbname + '..' + @Q2 + ' +   name + ' + @Q2 +      " out " + @dirname + @Q2 + ' + name + ' + @Q2 + '.txt '+ @bcpmode +   @security +           @Q2 + ' from ' + @dbname + '..sysobjects where type = ' + @Q2 +   'U' + @Q2 +      ' order by name'   execute(@myquery)   GO				

It is very important that you validate the row count returned by theexecution of bcp. To retrieve a row count of every table in the database,see the following article in the Microsoft Knowledge Base:
176426 : INF: How to Determine Number of Rows of Every Table in Database

The following is an example of the batch file created by this procedure(Bcpscript.bat):
   bcp pubs..authors out c:\bcp\authors.txt -c -Usa -P   bcp pubs..discounts out c:\bcp\discounts.txt -c -Usa -P   ...				

The following is an example of the bcp results, piped into a Results.txtfile:
   C:\>bcp pubs..authors out c:\bcp\authors.txt -c -Usa -P   Starting copy...   23 rows copied.   Network packet size (bytes): 4096   Clock Time (ms.): total =     31 Avg =      1 (741.94 rows per sec.)   C:\>bcp pubs..discounts out c:\bcp\discounts.txt -c -Usa -P   Starting copy...   3 rows copied.   Network packet size (bytes): 4096   Clock Time (ms.): total =      1 Avg =      0 (3000.00 rows per sec.)   ...				
records export download dump

Article ID: 176818 - Last Review: 12/05/2015 08:10:57 - Revision: 4.0

Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition

  • kbnosurvey kbarchive kbcode kbhowto KB176818