0

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.

John Doebrek
  • 11
  • 2
  • 3
    What would a single csv/xls file look like that contained 1300 tables? – dfundako Jun 13 '18 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 Jun 13 '18 at 21:26
  • @dfundako An excel workbook would consist of 1300 sheets, each representing a table. – John Doebrek Jun 13 '18 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 Jun 13 '18 at 21:33
  • @rd_nielsen I've briefly explored the sqlcmd option and have even tried a few commands. However, the data I'm accessing requires SQL Server Authentication as opposed to Windows Authentication. All the sqlcmd options I've found require the Windows Authentication with a password. If you have a work-around for this, please let me know. – John Doebrek Jun 13 '18 at 21:34
  • @dfundako Yes. I've made sure all the tables do not extend beyond the row limit and the objective is to have the data in a format that is easily accessible and searchable once we decommission the system. – John Doebrek Jun 13 '18 at 21:38
  • You should be able to use sqlcmd with SQL Server authentication. Google for examples (e.g.:https://www.mssqltips.com/sqlservertip/2478/connecting-to-sql-server-using-sqlcmd-utility/) – rd_nielsen Jun 13 '18 at 21:45
  • @rd_nielsen When attempting to follow those instructions, I get an error explaining that the SQL server "does not allow remote connections". Any ideas? – John Doebrek Jun 13 '18 at 21:58
  • Sorry, that's a server configuration issue that I can't address. You may find the answer online; otherwise, it ought to be a new question on SO. – rd_nielsen Jun 13 '18 at 22:07
  • The important question is: why? What is this 1300 sheet excel document being used for? It isn't something that a human can analyse. Is this for backup? Because the simplest thing to do is backup the database then restore that into free SQL Express and use it. The final purpose of the data dump helps us to understand what the real solution is – Nick.McDermaid Jun 13 '18 at 23:15
  • 2
    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 Jun 14 '18 at 05:59
  • 1300 sheets or 1300 CSV files.... it's not something for human consumption. There is some underlying requirement here that has not been explained that will likely be satisfied in other ways – Nick.McDermaid Jun 14 '18 at 08:12

2 Answers2

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

Anagha
  • 888
  • 1
  • 8
  • 17
SQLApostle
  • 552
  • 3
  • 15
  • 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 Jun 13 '18 at 23:09
  • 1
    Also , regarding your remote connections issue, you might wan't to try this : EXEC sp_configure 'remote access',1 reconfigure – SQLApostle Jun 13 '18 at 23:15
0

Perhaps this will help you to resolve your problem.

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

Irfan
  • 635
  • 7
  • 23