Export Data With Columns From SQL Server 2005 to Microsoft Excel Datasheet

--EXEC proc_generate_excel_with_columns 'Master','TESTA','C:\Testing.xls'


create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as


--Generate column names as a recordset


declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
--, @table_name varchar(100), @file_name varchar(max), @db_name varchar(100)
--SET @table_name='TESTA'
--SET @file_name='C:\Testing.xls'
--SET @db_name='master'
select 
@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
information_schema.columns
where 
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--select @columns 
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--select @data_file
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -SMinds8\Minds8 -Usa -P123456 -c -t'',no_output'
exec(@sql)


--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -SMinds8\Minds8 -Usa -P123456 -c -t'',no_output'
exec(@sql)


--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)


--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)