How to export whole SQL database to MS Excel Workbook

[Ned Bedinger]

Ronald Schwarz wrote:
> I have to document how 50% of the SQL tables in an application relate to
> the user interface configuration menus so that the user can trace
> through a configuration audit trail.

How many tables are we talking about?

>  
> Can someone explain how to export the entire SQL database to a MS Excel
> Workbook? 

Read a high-level description at:

http://www.mssqltips.com/tip.asp?tip=1202

Then read about the SQL Server Import Export Wizard online at MSDN.

I can only export one table at a time to a spreadsheet in
> order to capture the column headings of the tables. 

This sounds like the Bulk Copy method using bcp.exe (for a list of all 
the bcp parameters and options you don't need, and a few you might need, 
search MSDN or SQL Server Books Online for 'bcp export.' If you let bcp 
use its defaults (export the table as tab-delimited character data with 
line-end character for row-ends), then Excel ought to be able to suck it 
in. Not sure if bcp can write your data to Excel with the table name and 
everything, but if the only other choice you had was to go a-reading 
bushels of SQL Server documentation that may or may not lead to an 
updated procedure for exporting an entire database to Excel, I believe 
I'd go the default bcp route and make the necessary edits to get the 
worksheet names (and column data types, where useful) right.

For a few tables, I
> also need the records in order to view the values of various settings in
> the application.

My comment about data types is with respect to this requirement only.

>  
> The MS SQL Server Management Studio online help does not have an answer
> and I cannot write SQL or VBS scripts to automate the process.

I think you could pick that stuff up in a weekend. The hard part, the 
biggest time sink when ramping up on MS data acces code, at least when I 
learned it, was finding a source of good examples to copy -- the syntax 
for identifying and connecting to the database, and then exporting the 
data in a given format, was always wrong in the manuals, for years it 
seems but maybe that perception is due to how much of a drag it is to be 
misled by putative examples FROM THE VENDOR.


Anyway, actually, the tool for $29 that Jan suggested looks like a great 
value compared to the hour or two you'd spend wrangling with the Export 
Wizard, bcp, or any of the many other circuituous routes by which one 
might recreate an application configuration database in Excel after the 
application developers went to all the trouble of putting it in SQL 
Server.


Good luck.

Ned Bedinger
doc at edwordsmith.com