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

Question

I am using SQL Server 2008. Let's say I am creating a temporary table like this:

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

How can I get a list of fields dynamically? I would like to see something like this:

 Fields: col1 col2 

I thought about the sys.columns query, but it does not seem to store information about temporary tables. Any ideas?

Answer 1

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

Answer 2

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

Answer 3

Temporary tables are defined in tempdb, and table names are crippled.

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

Mark

Answer 4

Use information_schema and not encounter 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')) 

Answer 5

you can do it by following also ..

 create table #test (a int, b char(1)) select * From #test exec tempdb..sp_columns '#test' 

Answer 6

Anthony

try the following. this will give the expected ur 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%' 

Answer 7

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