Search code examples
sql-servertemp-tables

How can I get the list of columns from a temporary table?

I'd like to get the list of columns in a temporary table, similar to the INFORMATION_SCHEMA.columns view. However, this code:

select * 
from   tempdb.INFORMATION_SCHEMA.columns
where  TABLE_CATALOG = 'tempdb'
and    TABLE_NAME like '#myTemporaryTable%'

returns one row per column and per session. Is it safe to do this:

select distinct column_name,data_type 
from   tempdb.INFORMATION_SCHEMA.columns
where  TABLE_CATALOG = 'tempdb'
and    TABLE_NAME like '#myTemporaryTable%'

I have a feeling it isn't, even if you tighten up the like clause so it won't match myTemporaryTable and myTemporaryTable2.

Solution

  • If you really need query tempdb, I would use object_id

    SELECT  *
FROM    tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb..#myTemporaryTable')