How to retrieve field names from temporary table (SQL Server 2008)
SqlSql ServerSql Server-2008Temp TablesSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');
Solution 2 - Sql
select *
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '#MyTempTable%'
Solution 3 - Sql
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'))
Solution 4 - Sql
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
Solution 5 - Sql
you can do it by following way too ..
create table #test (a int, b char(1))
select * From #test
exec tempdb..sp_columns '#test'
Solution 6 - Sql
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%'
Solution 7 - Sql
select *
from tempdb.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=OBJECT_NAME(OBJECT_ID('#table'))