Bcp Data Extractions With The Column Names!

Ok, i am finally giving in on this one and asking for some help! I am trying to set up a T-SQL Statement that will extract data from all the tables in the current database to a csv file including Column names!

I know that bcp can not handle the column names, so i tried to get around this with an append of the column names from a select, but unfortunatly the select gives me the names in Alpha order and not the order of the fields.

I have tried putting in an order by on the select, but this does not seem to have any effect. I have included the snippet of my script that is causing the problem here :

-- set up the echo command
select @colcommand= 'exec master..xp_cmdshell'
+ ' '''
+ 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt'
+ ''''
from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE

and just in case you are interested in the rest of the script, the full monster is included at the bottom of the post. Also if you can see any more efficient ways of doing what i am trying to do, please let me know!

Thank you for your help in advance.

Mark

---------------------------------------------------------

-- Script to create a csv file of data from all tables inside current database

-- declare all variables
declare @command varchar(200) -- command used for bcp
declare @fetch_status int-- variable for fetch status in cursor

declare @TABLE varchar (200)-- Variable to hold table name
declare @colcommand varchar (200)-- Variable to hold column creation command
declare @count int-- Variable used to determine first itteration of Column loop
declare @names varchar(100) -- variable used for the column names
declare @delimiter varchar(10)-- variable used for delimiter in column names

SET @delimiter = ','-- set up the delimiter to comma
select @count=0-- initialises the COUNT variable

-- setup cursor to create the bcp command to backup the data files to csv format
declare bcpcommand cursor READ_ONLY FOR
select 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' '
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' c:cp'
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ '.txt' + ' -c -t,' + ' -T'
+ ' -S' + @@servername
+ ''''from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

-- setup cursor to pick up all the tables in the given database (used for column names section)
declare dbtables cursor READ_ONLY FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

open bcpcommand

select @fetch_status=0

while @fetch_status=0
begin
fetch next from bcpcommand into @COMMAND
select @fetch_status=@@fetch_status
if @fetch_status<>0
begin
continue
end

-- print 'Command to be run : ' + @COMMAND
EXEC (@COMMAND)
end

-- close and tidy up
close runme
deallocate runme

-- now create the fieldname files and then echo the 2 files together!

open dbtables
select @fetch_status=0
while @fetch_status=0
begin
fetch next from dbtables into @TABLE
select @fetch_status=@@fetch_status

if @fetch_status<>0
begin
continue
end

SELECT @names = COALESCE(@names + @delimiter, '') + name
FROM syscolumns where id = (select id from sysobjects where
name=@TABLE)

-- due to the concatonation used, the second itteration onwards has a , attached to the front of the line
-- this section removes the first char
if @count <> 0
begin
Select @names=SUBSTRING(@names,2,198)
end

-- set up the echo command
select @colcommand= 'exec master..xp_cmdshell'
+ ' '''
+ 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt'
+ ''''
from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE

-- print 'COMMAND : ' + @colcommand

exec (@colcommand)

-- reset @names variable for next itteration, and set count to 1 to trigger IF above
select @names=''
select @count=1
end

-- close and tidy up
close dbtables
deallocate dbtables