"You've been mostly dead all day!" - Fezzik, The Princess Bride
I was recently tasked with extracting data from a couple of dozen tables, tilde delimited, with column headings.
So, I spent a little time coming up with this:
set nocount on
select 'select '
select ''''+a.name+''''+'+''~''+'
from syscolumns a, sysobjects b
where a.id = b.id
and b.name = 'EXTRACT_TABLE'
order by colid;
select 'select ';
select case when a.xtype = 106 then 'isnull(cast('+a.name+' as varchar), '' '')'
when a.xtype = 108 then 'isnull(cast('+a.name+' as varchar), '' '')'
when a.xtype = 61 then 'isnull(convert(varchar(8),'+a.name+',112), '' '')'
else 'isnull('+a.name+','' '')' END+'+''~''+'
from syscolumns a, sysobjects b
where a.id = b.id
and b.name = 'EXTRACT_TABLE'
order by colid;
select 'from extract_table';
The output from this query needs a little tidying up - the last column of each generated select statement needs the +'~'+ removed from it (for obvious syntactical reasons) and the spacing of the output can always be cleaned up a little from a prettying it up perspective, but it got the job done.
This was from a SQL Server 2000 system and would need to be ever so slightly modified to use on 2005/2008/2012.
Like I said in the subject line, quick and dirty (but it got the job done) :)
