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

sql sql-server sql-server-2008 temp-tables
Anthony picture Anthony · Apr 16, 2009 · Viewed 106.7k times · Source

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?

Answer

kristof picture kristof · Apr 16, 2009 
select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');

Related questions

Temporary table record limit in Sql server

Is there any limits for the record in the temporary table.. I have tried with 1.3 million records.. may be i have to deal with billions in the future as the application demands.. Is it possible? If i could know the …

Read More
What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?

What is the difference between: CREATE TABLE #temp ( [ID] INT) INSERT INTO #temp SELECT ... and DECLARE @temp TABLE ( [ID] INT) INSERT @temp SELECT ... in SQL Server 2008?

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More