Finding the Data Types of a SQL Temporary Table

Finding the data types of a SQL temporary table

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

Get SQL Server temp table column type

The code below demonstrates how to get information for all the columns. Note that, in [tempdb], the table name is "____" with lots of underscores. So you have to access it as "LIKE __%", but this should work.

As a bonus, I added the code to get row counts.

As a note, I usually just "SELECT INTO .." then script out the table and manually create the temp table. But the code below will work for what you want.

CREATE TABLE #test
  (
       [id]     INT
       , [name] NVARCHAR(12)
  );

--
-- get columns and types
------------------------------------------------- 
SELECT [columns].[name]
       , [types].[name]
       , [columns].*
       , [types].*
FROM   [tempdb].[sys].[columns] AS [columns]
       JOIN [tempdb].[sys].[tables] AS [tables]
         ON [tables].[object_id] = [columns].[object_id]
       JOIN [sys].[types] AS [types]
         ON [types].[user_type_id] = [columns].[user_type_id]
WHERE  [tables].[name] LIKE N'#test__%'; 

--
-- get row count
------------------------------------------------- 
SELECT [objects].[name]                      AS [table]
       , [dm_db_partition_stats].[row_count] AS [row_count]
       , *
FROM   [tempdb].[sys].[dm_db_partition_stats] AS [dm_db_partition_stats]
       INNER JOIN [tempdb].[sys].[objects] AS [objects]
               ON [dm_db_partition_stats].[object_id] = [objects].[object_id]
WHERE  [objects].[name] LIKE '#test%'; 

Get structure of temp table (like generate sql script) and clear temp table for current instance

You need to use quotes around the temp table name and you can delete the temp table directly after using drop table ....

select *
into #myTempTable  -- creates a new temp table
from tMyTable  -- some table in your database

exec tempdb..sp_help '#myTempTable'

drop table #myTempTable

How to generate temp table columns and datatypes automatically via script

this might give you a start:

DECLARE @viewname VARCHAR(50);
SET @viewname ='tableorviewname';
SELECT  c.name + ' '+ t.name + 
case t.name  
WHEN 'varchar' THEN '('+CAST(c.max_length AS VARCHAR(3) )+'),'
ELSE ',' 
end
FROM sys.columns c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE object_id = (SELECT object_id from sys.objects where name = @viewname)
ORDER BY c.column_id

EDIT: TEMP TABLES:

temp tables are slightly different, for instance this works in sql 2008 for a temp table named #tv_source

DECLARE @viewortablename VARCHAR(50);
SET @viewortablename ='tempdb..#tv_source';
SELECT  c.name + ' '+ t.name + 
case t.name  
WHEN 'varchar' THEN '('+CAST(c.max_length AS VARCHAR(3) )+'),'
ELSE ',' 
end
FROM tempdb.sys.columns c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE object_id =  object_id(@viewortablename)
ORDER BY c.column_id

NOTES: this gives a comma separated list, but did NOT attempt to remove that last comma, it gives only a list, which you would likely want to put on a string and manipulate, etc. then use as a dynamic sql or somthing. Still, it should give you a start on what you wish to do.

NOTE for to others, sql 2000 would not display the lengths properly for instance on a varchar(45), it would just list the varchar part and I did not attempt to rework that for this question.

SQL statement to get column type

Using SQL Server:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_SCHEMA = 'yourSchemaName' AND
     TABLE_NAME   = 'yourTableName' AND 
     COLUMN_NAME  = 'yourColumnName'

Find the column names of Temp table

SELECT *
FROM   tempdb.sys.columns
WHERE  object_id = Object_id('tempdb..#sometemptable'); 

Get list of columns in a temp table in SQL Server

Your were close. Just needed to point it to Tempdb.Sys.Columns

 Select * From  Tempdb.Sys.Columns Where Object_ID = Object_ID('tempdb..#TempTable')

How do I return the SQL data types from my query?

select * from information_schema.columns

could get you started.

---

Related Topics