Menu Bar

Monday, March 19, 2012

Quick and Dirty Data Extract


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

No comments:

Post a Comment