Find the column names of Temp table

Question

I need to find the column names of temp table.

If it is a physical table then we can either use sys.columns or Information_schema.columns system views to find the column names.

Similarly is there a way to find the column names present in temp table?

Does this answer your question? [How to retrieve field names from temporary table (SQL Server 2008)](https://stackoverflow.com/questions/756080/how-to-retrieve-field-names-from-temporary-table-sql-server-2008) — casperOne, May 12 '23 at 18:49

score 36 · Accepted Answer · edited May 29 '15 at 20:02

36

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

edited May 29 '15 at 20:02

Pரதீப்

91,748
19
131
172

answered May 29 '15 at 17:19

William Xifaras

5,212
2
19
21

score 8 · Answer 2 · answered Jul 24 '19 at 09:55

To get only columns name you can use this query below:

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

To get columns name with data type you can use this query but you need to make sure sp_help runs in the same database where the table is located (tempdb).

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

you can achieve same result by joining against tempdb.sys.columns like below:

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

Find the column names of Temp table

2 Answers2

Linked