Export all tables to CSV files
-
Comments posted to this topic are about the item Export all tables to CSV files
-
That is disturbingly shorter than the 200 line Powershell script I wrote for that... in a great way
-
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'.
-
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
-
Love it.
Would love a similar script for that I can use for Oracle.
-
Good script, thanks.
-
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 rowsSample 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] -
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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply