Export complete SQL Server database to csv
Exporting complete SQL server database to CSV files. CSV cannot keep data for multiple worksheet as like excel files. So data for each table will be generated in separate files. SQL components used in this lecture are:
• BCP (Bluck copy program)
• xp_cmdshell
• STUFF
• FOR XML PATH
Script used in above lecture is:
DECLARE @query1 varchar(MAX) DECLARE @query2 varchar(MAX) DECLARE @table varchar(MAX)
SELECT ROW_NUMBER() over(
ORDER BY
(SELECT 1)) rownum, 'select ' + STUFF(
(SELECT ','+ 'Quotename(cast(' + ISNULL(COLUMN_NAME,'''''''') + ' as varchar(max)),''""'')' + ' as ""' + COLUMN_NAME + '"" '
FROM [Northwind].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = t.name
AND DATA_TYPE<>'image'
ORDER BY ordinal_position
FOR XML PATH('')),1,1,'') + ' FROM '+ '[Northwind].['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS col1,
'select ' + STUFF(
(SELECT ','+ 'Quotename(''' +COLUMN_NAME + ''',''""'')'
FROM [Northwind].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = t.name
AND DATA_TYPE<>'image'
ORDER BY ordinal_position
FOR XML PATH('')),1,1,'') AS col2,
SCHEMA_NAME(schema_id) + t.name AS col3 INTO #temp
FROM sys.tables t DECLARE @row int=0 WHILE
(SELECT count(1)
FROM #temp)>0 BEGIN
SELECT top 1 @query1=col2,
@query2=col1,
@row=rownum,@table=col3
FROM #temp DECLARE @sql varchar(8000)=''
SELECT @sql = 'bcp "' + @query1 + ' union all ' + @query2 + '" queryout C:\CSV\' + @table + '.csv -c -t, -T -S'+ @@servername
SELECT @sql EXEC master..xp_cmdshell @sql
DELETE
FROM #temp
WHERE rownum=@row END
DROP TABLE #temp
Below is script to enable xp_cmdshell:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',
1;
GO -- To update the currently configured value for advanced options.
RECONFIGURE;
GO -- To enable the feature.
EXEC sp_configure 'xp_cmdshell',
1;
GO -- To update the currently configured value for this feature.
RECONFIGURE;
GO
|