Extract whole SQL Server database to CSV file

Question

It is possible to extract all tables and rows of each table with one query or software in microsoft sql server?

thanks

Answer 1

You can do this by using the Import/Export Wizard. (Right Click database -> Tasks -> Import/Export Wizard)

Or for greater control you could use SSIS.

Answer 2

The above answer is correct but if this is something you want to script and do regularly, this might get you started:

exec sp_msforeachtable 'exec xp_cmdshell ''bcp database_name.? out c:\?.csv -S server_name -T -c -t ","'''

Caution... This uses an undocumented stored procedure and assumes that xp_cmdshell is enabled and that the xp_xmdshell account has rights to sql server.