SHARE
TWEET

Untitled

a guest Feb 27th, 2020 66 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top