"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) :)