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