SQL Server 2008

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!

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Leave a comment

Your email address will not be published. Required fields are marked *