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

• Microsoft SQL Server versions 6.0 and 6.5

SUMMARY

This article provides a script that uses the bulk copy program (bcp) to bulk copy all your user-defined tables on any given database. This script was designed to accommodate both integrated and standard security. You can also choose to specify either native or character mode bcp. The procedure in this article is coded to use character mode, but you can simply change one line to set the mode to native, if you want.

MORE INFORMATION

The script below dynamically codes a SELECT statement, using the parameters supplied to it, and builds a batch file containing the bcp instructions to get the data from your tables. You need to run this batch file, which will actually execute the bcp commands. Also note that you may not get the expected results if you are using extended characters. For more information on extended characters, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q153449
   TITLE     : INF: SQL Server Code Pages and AutoAnsiToOem Behavior

   isql -E /dpubs -Q "sp_bcpTablesOut 'pubs', 'c:\bcp\' "
   -oc:\bcp\bcpscript.bat /h-1 /n

   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.

   isql -E /dpubs -Q "sp_bcpTablesOut  'pubs', 'c:\bcp\' "
   -oc:\bcp\bcpscript.bat /h-1 /n

   isql -E /dpubs -Q "sp_bcpTablesOut  'pubs', 'c:\bcp\', 'SA' "
   -oc:\bcp\bcpscript.bat /h-1 /n

   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 in your 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

   ARTICLE-ID: Q176426
   TITLE     : INF: How to Determine Number of Rows of Every Table in
               Database

   bcp pubs..authors out c:\bcp\authors.txt -c -Usa -P
   bcp pubs..discounts out c:\bcp\discounts.txt -c -Usa -P
   ...

   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.)
   ...