An MSSQL stored procedure to export a table or view to CSV, with column headers, using BCP

sproc_exportObjectToCSV.sql

/****** Object:  StoredProcedure [dbo].[sproc_exportObjectToCSV]    Script Date: 03/02/2012 21:00:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

CREATE VIEW view_Columns AS SELECT '' AS COLUMN_NAME
GO

CREATE PROCEDURE [dbo].[sproc_exportObjectToCSV]
	@table_name varchar(255),
	@file_name varchar(1024)
AS 
BEGIN
	DECLARE @column_name varchar(255), @sql_column_names varchar(4000), @sql_columns varchar(4000), @sql varchar(4000), @db_name varchar(255)

	SET @db_name = DB_NAME()
	SET @sql_column_names = 'SELECT '
	SET @sql_columns = 'SELECT '
	EXEC('ALTER VIEW view_Columns AS SELECT COLUMN_NAME FROM OPENROWSET (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',''set fmtonly off exec ' + @db_name + '.dbo.sp_columns ''''' + @table_name + ''''''') AS tbl')
	DECLARE c1 CURSOR FOR SELECT COLUMN_NAME FROM view_Columns
	OPEN c1

	FETCH NEXT FROM c1 INTO @column_name
	WHILE @@FETCH_STATUS = 0 BEGIN
		
		SET @sql_column_names = @sql_column_names + '''\"' + @column_name + '\"'' AS ' + @column_name + ', '
		SET @sql_columns = @sql_columns + '''\"'' + CAST(REPLACE(' + @column_name + ',''\"'',''\"\"'') AS varchar(4000)) + ''\"'', '

		FETCH NEXT FROM c1 INTO @column_name
	END

	CLOSE c1
	DEALLOCATE c1

	SET @sql_column_names = LEFT(@sql_column_names,LEN(@sql_column_names)-1)
	SET @sql_columns = LEFT(@sql_columns,LEN(@sql_columns)-1) + ' FROM ' + @db_name + '.dbo.' + @table_name
	SET @sql = 'bcp "' + @sql_column_names + ' UNION ALL ' + @sql_columns + '" queryout "' + @file_name + '" -w -t , -T -S ' + @@servername
	EXEC master..xp_cmdshell @sql
END

GO