MS SQL Server 2008: How to export all the tables into CSV?

Solution 1:

A more robust solution is to use the Oracle Migration Workbench. This used to be a separate utility from Oracle, but is now included in Oracle SQL Developer . If you and CLOB/BLOB data types then this is the way to go. You should also read the Oracle documentation on how to migrate as the SQL Developer migration interface is not very intuitive.

If you do want to go down the road of exporting all tables to csv you can use BCP or SSIS. There is a very good article on simple-talk on using some t-sql and to generate bcp export commands for all tables in a database. You can also create simple SSIS package to export all the tables to csv by using a flat file destination task.

here's something quick & dirty that will generate the BCP commands for you. Run it in SSMS, the run the output at the command prompt.

            USE AdventureWorks
SELECT 'bcp ' +
            QUOTENAME(DB_NAME())
            + '.'
            +QUOTENAME(SCHEMA_NAME(schema_id))
            + '.'
            +QUOTENAME(name)
            + ' '+'out'+' '
            + name + '.csv -w -t"|" -E -S '
            + @@servername
            + ' -T'
    FROM sys.objects
    WHERE TYPE='u'
        AND is_ms_shipped=0