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?

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);

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 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 other answers will give you the information that you need, but still require you to type it all out when you define the table variable.

The following TSQL will allow you to quickly generate the table variable's definition for any given table.

This can save you a lot of time instead of manually typing table definitions like:

table(Field1Name nvarchar(4), Field2Name nvarchar(20), Field3Name int
, Field4Name numeric(28,12))

TSQL:

select top 10 * 
into #temp
from db.dbo.myTable



declare @tableName nvarchar(max)
set @tableName = '#temp'

use tempdb
declare @tmp table(val nvarchar(max))
insert into @tmp 
select case data_type	
	when 'binary' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
	when 'char' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
	when 'datetime2' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(DATETIME_PRECISION as nvarchar(max)) + ')'
	when 'datetimeoffset' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(DATETIME_PRECISION as nvarchar(max)) + ')'
	when 'decimal' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(NUMERIC_PRECISION as nvarchar(max)) + ',' + cast(NUMERIC_SCALE as nvarchar(max)) + ')'
	when 'nchar' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
	when 'numeric' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(NUMERIC_PRECISION as nvarchar(max)) + ',' + cast(NUMERIC_SCALE as nvarchar(max)) + ')'
	when 'nvarchar' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
	when 'time' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(DATETIME_PRECISION as nvarchar(max)) + ')'
	when 'varbinary' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
	when 'varchar' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
	-- Most standard data types follow the pattern in the other section.  
    -- Non-standard datatypes include: binary, char, datetime2, datetimeoffset, decimal, nvchar, numeric, nvarchar, time, varbinary, and varchar
    else COLUMN_NAME + ' ' + DATA_TYPE
          
	end +  case when IS_NULLABLE <> 'YES' then ' NOT NULL' else '' end 'dataType'
	 from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like @tableName + '%'

declare @result nvarchar(max)
set @result = ''
select @result = @result + [val] + N','
from @tmp
where val is not null

set @result = substring(@result, 1, (LEN(@result)-1))

-- The following will replce '-1' with 'max' in order to properly handle nvarchar(max) columns
set @result = REPLACE(@result, '-1', 'max')
select @result

Output:

Field1Name nvarchar(4), Field2Name nvarchar(20), Field3Name int
, Field4Name numeric(28,12)

to get columns name with data type use this

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

or To get only columns name to use this

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

What you are trying to do is to get information about the system types of the columns you are querying.

For SQL Server 2012 and later you can use sys.dm_exec_describe_first_result_set function. It returns very detailed information about the columns and the system_type_column holds the complete system type definition (ready to use in your table definition):

For example:

SELECT * 
FROM [sys].[dm_exec_describe_first_result_set] (N'SELECT object_id, name, type_desc FROM sys.indexes', null, 0);

Yes, the data types of the temp table will be the data types of the columns you are selecting and inserting into it. So just look at the select statement and determine each data type based on the column you select.

I'd go the lazy route and use

use tempdb
GO
EXECUTE sp_help #temp

Finding the data types of a SQL temporary table

METHOD 1 – Using SP_HELP

EXEC TempDB..SP_HELP #TempTable;

Note-

In the Table Structure, the Table Name shows something like ‘#TempTable__________________________________________________________________________________________________________0000000004CB’. Actually, the total length of each and every Temp Table name will be 128 . To handle the Same Temp Table name in Multiple Sessions differently, SQL Server will automatically add some underscores in between and alphanumeric’s at end.

METHOD 2 – Using SP_COLUMNS

EXEC TempDB..SP_COLUMNS '#TempTable';

METHOD 3 – Using System Tables like INFORMATION_SCHEMA.COLUMNS, SYS.COLUMNS, SYS.TABLES

SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (
SELECT NAME FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable')
);
GO

SELECT * FROM TempDB.SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO

SELECT * FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO