We had a situation come up where we needed to quickly export about 3000 tables from SQL Server to CSV. We bought a tool in hopes that we could do this quickly (Why wont DTS export to multiple text files?!?), well the tool stunk and it would have literally taken a week to complete. So I wrote a two SP's that use BCP to export an entire database or a single table to CSV (Or use whatever delimiter and qualifier you want). I whiped them up pretty quick so they could probably use some fine tuning if you are going to use them in production. Two things to note:

1) In SQL Server 2005 you have to explicitly turn on the ability to use xp_cmdshell in order to make the BCP call.

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

2) Since the table selection is built on the fly (To add delimiters, qualifiers, column names and escape chars in data that match the qualifier) tables with a lot of columns will fail. The selection statement is peiced together in a VARCHAR(8000) so there is limited space.

3) I'm creating a temporary view in which to pull the exported data from because the call to xp_cmdshell is limited to 1k in SQL Server 2000 (I tried it without the view in 2005 and it doesent appear to have this limitation). 

Here is the proc that exports the entire DB:

CREATE PROCEDURE [dbo].[ExportDatabase]
@Database varchar(256),
@OutputPath varchar(1000),
@OutputExtension varchar(50) = 'csv',
@Delimiter varchar(50) = ',',
@Qualifier varchar(50) = '"',
@IncludeColumnHeaders bit = 1
AS
BEGIN

DECLARE @TableName varchar(256)

DECLARE @ColumnCursorSQL varchar(500)
SET @ColumnCursorSQL = 'DECLARE TableCursor CURSOR FAST_FORWARD FOR SELECT name FROM ' + @Database + '..' + 'sysobjects WHERE type=''U'''
EXEC (@ColumnCursorSQL)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    EXEC ExportTable
    @Database,
    @TableName,
    @OutputPath,
    @OutputExtension,
    @Delimiter,
    @Qualifier,
    @IncludeColumnHeaders

    FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor
DEALLOCATE TableCursor

END

Here is the proc that exports a single table:

CREATE PROCEDURE [dbo].[ExportTable]
@Database varchar(256),
@Table varchar(256),
@OutputPath varchar(1000),
@OutputExtension varchar(50) = 'csv',
@Delimiter varchar(50) = ',',
@Qualifier varchar(50) = '"',
@IncludeColumnHeaders bit = 1
AS
BEGIN

DECLARE @ColumnNameSQL varchar(8000)
DECLARE @SelectSQL varchar(8000)
DECLARE @ColumnName varchar(256)
DECLARE @FirstColumn bit

SET @FirstColumn = 1

SET @SelectSQL = 'SELECT '
SET @ColumnNameSQL = 'SELECT '

DECLARE @ColumnCursorSQL varchar(500)
SET @ColumnCursorSQL = 'DECLARE ColumnCursor CURSOR FAST_FORWARD FOR SELECT name FROM ' + @Database + '..' + 'syscolumns WHERE id=object_id(''' + @Database + '..' + @Table + ''')'
EXEC (@ColumnCursorSQL)

OPEN ColumnCursor

FETCH NEXT FROM ColumnCursor INTO @ColumnName

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF @FirstColumn = 1
        SET @FirstColumn = 0
    ELSE
        BEGIN
            SET @SelectSQL = @SelectSQL + @Delimiter
            IF @IncludeColumnHeaders = 1
                SET @ColumnNameSQL = @ColumnNameSQL + @Delimiter
        END

    IF @IncludeColumnHeaders = 1
        SET @ColumnNameSQL = @ColumnNameSQL + '''' +
        @Qualifier + @ColumnName + @Qualifier + ''' AS ' + @ColumnName

    SET @SelectSQL = @SelectSQL + '''' + @Qualifier +
        ''' + REPLACE(CONVERT(varchar(8000), ' + @ColumnName +
        '), ''' + @Qualifier + ''', ''' + @Qualifier + @Qualifier + ''') + ''' + @Qualifier + ''' AS ' + @ColumnName

    FETCH NEXT FROM ColumnCursor INTO @ColumnName

END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

SET @SelectSQL = @SelectSQL + ' FROM ' + @Database + '..' + @Table

IF @IncludeColumnHeaders = 1
    SET @SelectSQL = @ColumnNameSQL + ' UNION ALL ' + @SelectSQL

DECLARE @ExportTempViewName varchar(50)
SET @ExportTempViewName = + 'ExportTemp' + REPLACE(CONVERT(varchar(36), NEWID()), '-', '')
DECLARE @ExportTempViewCreate varchar(8000)
SET @ExportTempViewCreate = 'CREATE VIEW ' + @ExportTempViewName + ' AS ' + @SelectSQL

EXEC (@ExportTempViewCreate)

SET @SQL = 'bcp "SELECT * FROM ' + db_name() + '..' + @ExportTempViewName + '" queryout "' +
    @OutputPath + '\' + @Database + '.' + @Table + '.' +
    @OutputExtension + '" -c -t, -T -S' + @@servername

EXEC master..xp_cmdshell @SQL

DECLARE @ExportTempViewDrop varchar(8000)
SET @ExportTempViewDrop = 'DROP VIEW ' + @ExportTempViewName

EXEC (@ExportTempViewDrop)

END