A Nifty SQL for T-SQL (Sybase and MS-SQL)

A nifty SQL for T-SQL (Sybase and MSSQL) [web development] - ramon - ramon@thebuckland.com @ 12:31:24

When playing with SQL I often find that I create some massive SQL statement .. like

select blah,blah, (case foo when ... end)
from ...
and i need to put that into a temp table.. what should the temp table look like was always the next step. So .. thinking about it
I decided to hack up my solution.

Insert the select statement into a #temp_table
select *
into #my_tmp_table
from ...
and then I wrote this whopper to extract out of the sys tables
what the Temp table looks like as a CREATE statement

It's a bit crude but it will give the basic overview of what you want

select sc.name +  ' ' + st.name +
       (case st.name
     when 'varchar' then
         '(' + convert(varchar,sc.length) + ') ' + (case status when 0 then 'not null' else 'null' end) + ','
     when 'numeric' then
         '(' + convert(varchar,sc.prec) + ',' + convert(varchar,sc.scale) + ') ' + (case status when 0 then 'not null' else 'null' end) + ','
     when 'decimal' then
         '(' + convert(varchar,sc.prec) + ',' + convert(varchar,sc.scale) + ') ' + (case status when 0 then 'not null' else 'null' end) + ','
     else
         ' ' + (case status when 0 then 'not null' else 'null' end) + ','
      end) ,-- sc.*
from tempdb..syscolumns sc
  inner join systypes st
  on st.usertype = sc.usertype
where id in (select id
           from tempdb..sysobjects
          where name like "#transaction%")
HTH.

Ramon