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

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?

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

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

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

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

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

you can do it by following way too ..

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

select * From #test

exec tempdb..sp_columns '#test'