Export all tables to CSV files

Question

Post reply

Export all tables to CSV files

gonzamole

SSC-Addicted

Points: 425
More actions
March 9, 2016 at 10:43 pm

#311977

Comments posted to this topic are about the item Export all tables to CSV files

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

michael.lambert SSC Rookie Points: 29 More actions · Answer 1

That is disturbingly shorter than the 200 line Powershell script I wrote for that... in a great way 🙂

dhart SSC Journeyman Points: 98 More actions · Answer 2

When I run this and then try to execute one of the sample rows

Sample results:

bcp "SELECT * FROM [XXXapp].[dbo].[tsmReportSetting]" queryout c:\tsmReportSetting.csv -c -t, -T -S XXXappServerName

I'm getting this error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'queryout'.

bernard.beckett SSC Veteran Points: 230 More actions · Answer 3

Hi,

I had the same issue wrt the "queryout" error,

So I wrote the below, and it works 100%, for me.

I changed the code to pickup all tables from all Databases on the server. It's slightly longer than the original code, but for what I needed, it works 🙂

Let me know what you guys think.

Also, this is my first post on sqlservercentral.com 😀

IF object_id('tempdb..###tmp_DB') IS NOT NULL BEGIN DROP TABLE ###tmp_DB END
IF object_id('tempdb..###tmp_Tables') IS NOT NULL BEGIN DROP TABLE ###tmp_Tables END
IF object_id('tempdb..###tmp_Tables_tmp') IS NOT NULL BEGIN DROP TABLE ###tmp_Tables_tmp END
SELECT '[' + Name + ']' DBName, database_id INTO ###tmp_DB FROM sys.databases ORDER BY Name
DECLARE @DB_Loop VARCHAR(255), @SQL_Text NVARCHAR(Max), @SQL_Text_2 VARCHAR(8000), @Schemas VARCHAR(255), @Tables VARCHAR(255), @FileLocation VARCHAR(50)
SET @FileLocation = 'FILEPATH\Results.csv'
CREATE TABLE ###tmp_Tables_tmp (DBName VARCHAR(255), [SchemaName] VARCHAR(255), [TableName] VARCHAR(255))
CREATE TABLE ###tmp_Tables (DBName VARCHAR(255), [SchemaName] VARCHAR(255), [TableName] VARCHAR(255))
SELECT @DB_Loop = MIN(DBName) FROM ###tmp_DB
WHILE @DB_Loop IS NOT NULL
BEGIN
TRUNCATE TABLE ###tmp_Tables_tmp
SET @Schemas = (@DB_Loop + '.sys.schemas')
SET @Tables = (@DB_Loop + '.sys.tables')
SELECT @SQL_Text = 'INSERT INTO ###tmp_Tables_tmp SELECT 1 as DB, ''['' + S.name + '']'' SchemaName, ''['' + T.name + '']'' TableName FROM ' + @Schemas + ' S INNER JOIN ' + @Tables + ' T ON (T.schema_id = S.schema_id) ORDER BY 1, 2';
EXEC sp_executesql @SQL_Text
UPDATE ###tmp_Tables_tmp SET DBName = @DB_Loop
INSERT INTO ###tmp_Tables
SELECT * FROM ###tmp_Tables_tmp
TRUNCATE TABLE ###tmp_Tables_tmp
SELECT @DB_Loop = MIN(DBName) FROM ###tmp_DB WHERE DBName > @DB_Loop
END
SELECT * FROM ###tmp_Tables
SELECT @SQL_Text_2 = 'bcp "SELECT * FROM ###tmp_Tables" queryout ' + @FileLocation + ' -T -c -t , -S "SERVERNAME"'
EXEC master..xp_cmdshell @SQL_Text_2

terry.home Ten Centuries Points: 1004 More actions · Answer 4

Love it.

Would love a similar script for that I can use for Oracle.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

March 28, 2016 at 6:54 am

#1867942

Good script, thanks.

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

I LOVE utility scripts like this that build off of metadata, it's a great way to learn how SQL Server stores information internally. Thanks!

dhart (3/25/2016)
When I run this and then try to execute one of the sample rows
Sample results:
bcp "SELECT * FROM [XXXapp].[dbo].[tsmReportSetting]" queryout c:\tsmReportSetting.csv -c -t, -T -S XXXappServerName
I'm getting this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'queryout'.

Change queryout to out and it should work.

From BOL:

out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.

queryout copies from a query and must be specified only when bulk copying data from a query.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Wayne West SSC-Insane Points: 22586 More actions · Answer 7

Bernard, I'm very impressed by your script! Very cool work!

I'm also sad to inform you that you can do it in one line of code: there are two undocumented stored procedures in SQL Server called sp_MSforeachdb and sp_MSforeachtable. You could combine Gonzalo's code with sp_MSforeachdb and get it done pretty sweet.

http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

And welcome to the zoo! You don't have to be crazy to be here, but..., well, yes. Yes, you do have to be crazy to be here. :w00t:

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]