March 9, 2016 at 10:43 pm
Comments posted to this topic are about the item Export all tables to CSV files
March 25, 2016 at 11:10 am
That is disturbingly shorter than the 200 line Powershell script I wrote for that... in a great way
March 25, 2016 at 2:47 pm
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'.
March 25, 2016 at 3:45 pm
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
March 26, 2016 at 10:51 am
Love it.
Would love a similar script for that I can use for Oracle.
March 28, 2016 at 6:54 am
Good script, thanks.
March 28, 2016 at 4:39 pm
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]
March 29, 2016 at 1:31 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy