How to retrieve field names from temporary table (SQL Server 2008)

Question

I'm using SQL Server 2008. Say I create a temporary table like this one:

create table #MyTempTable (col1 int,col2 varchar(10))

How can I retrieve the list of fields dynamically? I would like to see something like this:

Fields:
col1
col2

I was thinking of querying sys.columns but it doesn't seem to store any info about temporary tables. Any ideas?

kristof · Accepted Answer · 2009-04-16T13:51:32.477

138

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');

edited Apr 16 '09 at 13:51

answered Apr 16 '09 at 13:27

kristof

52,923
24
87
110

Good ans. Use `select name from tempdb.sys.tables` get all the temporary table names – Raaghav Jul 26 '16 at 07:35
3

Verified that this method doesn't return data from #temp tables created via a different connection. The alternatives using LIKE #temp% do. – Morvael Dec 20 '17 at 08:09

score 30 · Answer 2 · answered Apr 16 '09 at 13:27

30

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '#MyTempTable%'

answered Apr 16 '09 at 13:27

Ed Guiness

34,602
16
110
145

4

This answer is better. The table name is included as well as the datatype for the column. If your where clause contains a list of temp tables, then this is even more superior because you can differentiate which columns come from what temp table – VISQL Oct 31 '12 at 18:08
1

@VISQL Actually, the accepted answer is better because it does not use `INFORMATION_SCHEMA`. You can easily get the datatypes by JOINing to `sys.types` and you can easily get the table names via `OBJECT_NAME( object_id , database_id )` or JOINing to `tempdb.sys.tables`. This is all that the `INFORMATION_SCHEMA` Views do. – Solomon Rutzky Apr 02 '16 at 05:54
1

The problem with this is that if you are using 2 temp tables with similar names (e.g #TEMP1 and #TEMP12) then when using #TEMP1 as the table name you also get the columns for #TEMP12 – apc Mar 22 '17 at 15:26
Using '#MyTempTable[_][_][_]%' works better to ensure you don't get other tables whcih start with name of the table you are looking for – apc Mar 22 '17 at 15:30
2

Downvoted as: Verified that the accepted answer doesn't return data from #temp tables created via a different connection. This method does. The accepted answer is much safer. – Morvael Dec 20 '17 at 08:07

score 8 · Answer 3 · answered Jun 13 '13 at 22:17

To use information_schema and not collide with other sessions:

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name =
    object_name(
        object_id('tempdb..#test'),
        (select database_id from sys.databases where name = 'tempdb'))

score 7 · Answer 4 · answered Apr 16 '09 at 13:26

7

The temporary tables are defined in "tempdb", and the table names are "mangled".

This query should do the trick:

select c.*
from tempdb.sys.columns c
inner join tempdb.sys.tables t ON c.object_id = t.object_id
where t.name like '#MyTempTable%'

Marc

answered Apr 16 '09 at 13:26

marc_s

732,580
175
1,330
1,459

2

I think this could cross scopes. If it's one time code, fine. If it's code that will have a real lifespan, problem. – jcollum Jul 28 '09 at 19:32
1

Yep, bad solution - if multiple connections created temp tables with the same name (eg in a proc that gets called from an application), this will be wrong. – Tao Apr 13 '11 at 12:37
Downvoted as: Verified that the accepted answer doesn't return data from #temp tables created via a different connection. This method does. The accepted answer is much safer. – Morvael Dec 20 '17 at 08:08

score 3 · Answer 5 · answered Apr 24 '13 at 05:55

3

you can do it by following way too ..

create table #test (a int, b char(1))

select * From #test

exec tempdb..sp_columns '#test'

answered Apr 24 '13 at 05:55

Nishad

426
2
7
20

provides everything needed to write the explicit `CREATE TABLE`: `exec tempdb..sp_columns '#test'` – yzorg Apr 06 '15 at 14:15

score 1 · Answer 6 · answered Nov 27 '12 at 13:54

1

Anthony

try the below one. it will give ur expected output

select c.name as Fields from 
tempdb.sys.columns c
    inner join tempdb.sys.tables t
 ON c.object_id = t.object_id
where t.name like '#MyTempTable%'

answered Nov 27 '12 at 13:54

Franklin

895
1
11
19

score 0 · Answer 7 · answered Oct 09 '17 at 13:50

0

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME=OBJECT_NAME(OBJECT_ID('#table'))

answered Oct 09 '17 at 13:50

elle0087

840
9
23

How to retrieve field names from temporary table (SQL Server 2008)

7 Answers7

Linked