Thursday 21 August 2003

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

A nifty SQL for T-SQL (Sybase and MSSQL) - @ 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

Current 5 booksmarks @ del.icio.us/pefdus