TechSapphire Wednesday, 2018-02-21, 12:03 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • 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
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018