Find the datatypes of a SQL #temp table

I’m sure you’ve done it a million times like I have: selected a chunk of data into a #temp table for some quick and dirty ad_hoc analysis.

Ever wanted to know what data types were assigned to the temp table?

This will get you there.

EXEC [tempdb].[dbo].[sp_help] N'#temptable';

or:

SELECT 
  c.name, 
  c.column_id, 
  c.system_type_id, 
  t.name, 
  c.max_length, 
  t.precision 
FROM tempdb.sys.columns c
JOIN tempdb.sys.types t ON c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID(N'tempdb..#temptable');

Tested on SQL Server versions 2005, 2008 and 2012. Enjoy!