Export all tables of database to Excel

Question

Hi to everybody

How can I export all tables of a database to one excel file (each table should be one spreadsheet in the excel file) by using SQL Code?

Important remarks:
- The export should be generated via (T-)SQL Code not else how- The Excel File should be created by SQL Code (it does not exist in advance)
- The tablenames are dynamic; I don't know the names in advance. Therefore the skript should dynamically read table names (using something like a for-loop)
- I am using SQL Server 2008 R2
- I am using MS Office 2007

Thanks a lot for your support in advance
Angeline

Answer 1

Hi Angeline,
You can find detailed description here

http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/

Answer 2

Also see http://social.msdn.microsoft.com/Forums/en/transactsql/thread/c943caf6-1fc7-46d2-9401-c047f479fd64

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 3

>The export should be generated via (T-)SQL Code

There is no T-SQL command for that.

Best to use the SSIS Import/Export Wizard:

http://www.sqlusa.com/bestpractices/ssis-wizard/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

Basically you will have to write the sql query on all the tables which you want to export, there is no inbuilt way

here is the example with PowerShell how you can export your sql query in excel. check this link

http://sethusrinivasan.com/2012/04/06/export-the-query-to-excel-sheet-automatically/

http://sqlserverpedia.com/blog/sql-server-bloggers/powershell-and-sql/

---

Mark this post as answer if this resolves your issue.

---

Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin