Finding the data types of a SQL temporary table

you need to qualify the sp_help process to run from the tempdb database to get details about a hash table, because that's where the hash table is actually stored. If you attempt to run sp_help from a different database you'll get an error that the table doesn't exist in that database.

If your query is executing outside of tempdb, as I assume it is, you can run the following:

exec tempdb..sp_help #temp

One benefit of this procedure is it includes a text description of the column datatypes for you. This makes it very easy to copy and paste into another query, e.g. if you're trying use the definition of a temp table to create a table variable.

You could find the same information in the Syscolumns table, but it will give you numeric indentifiers for the types which you'll have to map yourself. Using sp_help will save you a step.

The accepted answer does not give the data type.Joining tempdb.sys.columns with sys.types gives the data type as mentioned in the comment of the answer.But joining on system_type_id yields one extra row with datatype "sysname". Instead "user_type_id" gives the exact solution as given below.

SELECT cols.NAME
 ,ty.NAME
FROM tempdb.sys.columns cols
JOIN sys.types ty ON cols.user_type_id = ty.user_type_id
WHERE object_id = OBJECT_ID('tempdb..#temp')

You need to make sure sp_help runs in the same database where the table is located (tempdb). You can do this by prefixing the call directly:

EXEC tempdb.dbo.sp_help @objname = N'#temp';

Or by prefixing a join against tempdb.sys.columns:

SELECT [column] = c.name, 
       [type] = t.name, c.max_length, c.precision, c.scale, c.is_nullable 
    FROM tempdb.sys.columns AS c
    INNER JOIN tempdb.sys.types AS t
    ON c.system_type_id = t.system_type_id
    AND t.system_type_id = t.user_type_id
    WHERE [object_id] = OBJECT_ID(N'tempdb.dbo.#temp');

This doesn't handle nice things for you, like adjusting max_length for varchar differently from nvarchar, but it's a good start.

In SQL Server 2012 or better, you can use a new DMF to describe a resultset, which takes that issue away (and also assembles max_length/precision/scale for you). But it doesn't support #temp tables, so just inject the query without the INTO:

SELECT name, system_type_name, is_nullable
  FROM sys.dm_exec_describe_first_result_set(N'SELECT 
        a.col1, 
        a.col2, 
        b.col1... 
      --INTO #temp
      FROM ...;',NULL,1);