UDTs in temp table def

[DougXP]

Hi

When I try to create a temp table with columns of a user defined type I get an error: 2715 Can't find type...

I realise this is because the UDTs are not present in the tempdb and I don't want to create them there.

Is there a solution?

 

[scriptdan]

try building the temp table by using select into.

i.e if you have tableA with columns that are UDT, you can
run

select * into tempdb..tableB
from yourdb..tableA
where 0=1 -- false value here simply will cause
-- empty table structure to be created
-- and underlying type of UDT col from
-- original table will be used.
go
use tempdb
exec sp_help tableB
go

 

[scriptdan]

Actually in my previous post I should note it is better to use #tableB as opposed to tempdb..tableB. The former is truly a temp table, while the latter will remain in tempdb until you drop it.