Get SQL Server temp table column type

Question

I'm loading CSVs into temporary tables like this:

select * 
into #Temp
from openrowset('Microsoft.ACE.OLEDB.12.0', 
                'Text;Database=D:\DataFolder;', 
                'SELECT * from [DataFile.csv]') as x;

The OLEDB driver determines a type for each column.

But temporary tables do not appear in INFORMATION_SCHEMA.COLUMNS.

How can I determine the data type for each column of the resulting #Temp table?

Answer 1

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%';