Export a Large SQL Database via Query

Question

I am trying to export a database within Microsoft SQL Server Management Studio 2000. My database is extremely large with over 1,300 unique tables.

I am aware of the export wizard and copy/paste options but am looking for something that would allow me to export all 1,300 tables (or at least a few hundred at once) into a single csv or xls file. Copy and pasting or selecting "save as" for each table would take far too long and the export wizard only allows a few dozen tables at a time.

What would a single csv/xls file look like that contained 1300 tables? — dfundako
– dfundako, Commented Jun 13, 2018 at 21:18
Do you really have to do it from within SSMS? Scripting the exports using sqlcmd or some other script processor would allow you to export them all at once, and should require less of your time.. — rd_nielsen
– rd_nielsen, Commented Jun 13, 2018 at 21:26
@dfundako An excel workbook would consist of 1300 sheets, each representing a table. — John Doebrek
– John Doebrek, Commented Jun 13, 2018 at 21:30
@JohnDoe I would enjoy seeing people scrolling through that. Also, excel workbooks only can do a million or so rows, so keep that in mind. — dfundako
– dfundako, Commented Jun 13, 2018 at 21:33
I guess I'll speak for everyone here if I ask why a single file like Excel would be of advantage. Most probably CSV files for each table would be much easier to tackle the problem. Just because you'd have to access to a single Excel file, it doesn't mean the tasks gets easier because you'd still have the sheets. Besides, I'd be not surprised if 1300 sheets would hit some sort of limit by Excel. They point is, a such idea would be highly experimental in my opinion and I wouldn't do that for production stuff — ksauter
– ksauter, Commented Jun 14, 2018 at 5:59

Anagha · Accepted Answer · 2018-06-14 06:17:51Z

1

Well , I would not recommended you to do this, but if you desperately need a solution in the way you have described , here it is :

First , run this query on the database :

    SELECT 'sqlcmd -S . -d '+DB_NAME()+' -E -s, -W -Q "
    SET NOCOUNT ON;
    SELECT * FROM '+table_schema+'.'+TABLE_name+'" > "C:\Temp\'+Table_Name+'.csv"' 
    FROM [INFORMATION_SCHEMA].[TABLES]

You might want to change the folder name as per your convenience

Second, Copy all the rows returned into a Export.bat or Export.cmd file

Third, run the Export.bat file to get 1300 odd tables you need in separate CSV files

Fourth, open a cmd window , navigate to the folder you have your files in and use the following command :

copy *.csv Export.csv

You will have a single Export.csv file containing all your tables, along with headers for each table

edited Jun 14, 2018 at 6:17

Anagha

9181 gold badge8 silver badges17 bronze badges

answered Jun 13, 2018 at 23:07

SQLApostle

5903 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SQLApostle Over a year ago

For SQL authentication, use : SELECT 'sqlcmd -S . -d '+DB_NAME()+' -U [username] -P [password] -s, -W -Q "SET NOCOUNT ON;SELECT * FROM '+table_schema+'.'+TABLE_name+'" > "C:\Temp\'+Table_Name+'.csv"' FROM [INFORMATION_SCHEMA].[TABLES]

SQLApostle Over a year ago

Also , regarding your remote connections issue, you might wan't to try this : EXEC sp_configure 'remote access',1 reconfigure

Irfan · Accepted Answer · 2018-06-14 06:56:52Z

0

Perhaps this will help you to resolve your problem.

SQL Server Management Studio 2012 - Export all tables of database as csv

edited Jun 14, 2018 at 6:56

answered Jun 14, 2018 at 6:29

Irfan

7057 silver badges30 bronze badges

Collectives™ on Stack Overflow

Export a Large SQL Database via Query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related