woggles woggles - 4 months ago 13
SQL Question

Finding the data types of a SQL temporary table

I need to switch from using a #temp table to a @table variable so that I can use it in a function.

My query uses insert into #temp (from multiple tables) like so:

SELECT
a.col1,
a.col2,
b.col1...
INTO #temp
FROM ...


Is there an easy way to find out the data types of the columns in the #temp table so that I can create the @table variable with the same columns and data types as #temp?

Answer
EXEC tempdb.dbo.sp_help N'#temp';

or

SELECT * 
    FROM tempdb.sys.columns 
    WHERE [object_id] = OBJECT_ID(N'tempdb..#temp');