Guest User

Untitled

a guest
Feb 27th, 2020
72
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @query1 varchar(MAX) DECLARE @query2 varchar(MAX) DECLARE @table varchar(MAX)
  2. SELECT ROW_NUMBER() over(
  3. ORDER BY
  4. (SELECT 1)) rownum, 'select ' + STUFF(
  5. (SELECT ','+ 'Quotename(cast(' + ISNULL(COLUMN_NAME,'''''''') + ' as varchar(max)),''""'')' + ' as ""' + COLUMN_NAME + '"" '
  6. FROM [Northwind].INFORMATION_SCHEMA.COLUMNS
  7. WHERE TABLE_NAME = t.name
  8. AND DATA_TYPE<>'image'
  9. ORDER BY ordinal_position
  10. FOR XML PATH('')),1,1,'') + ' FROM '+ '[Northwind].['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS col1,
  11. 'select ' + STUFF(
  12. (SELECT ','+ 'Quotename(''' +COLUMN_NAME + ''',''""'')'
  13. FROM [Northwind].INFORMATION_SCHEMA.COLUMNS
  14. WHERE TABLE_NAME = t.name
  15. AND DATA_TYPE<>'image'
  16. ORDER BY ordinal_position
  17. FOR XML PATH('')),1,1,'') AS col2,
  18. SCHEMA_NAME(schema_id) + t.name AS col3 INTO #temp
  19. FROM sys.tables t DECLARE @row int=0 WHILE
  20. (SELECT count(1)
  21. FROM #temp)>0 BEGIN
  22. SELECT top 1 @query1=col2,
  23. @query2=col1,
  24. @row=rownum,@table=col3
  25. FROM #temp DECLARE @sql varchar(8000)=''
  26. SELECT @sql = 'bcp "' + @query1 + ' union all ' + @query2 + '" queryout C:\CSV\' + @table + '.csv -c -t, -T -S'+ @@servername
  27. SELECT @sql EXEC master..xp_cmdshell @sql
  28. DELETE
  29. FROM #temp
  30. WHERE rownum=@row END
  31. DROP TABLE #temp
RAW Paste Data