Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 795
Anthony Avatar asked Apr 16 '09 13:04

Anthony


People also ask

How do I get the column names for a temp table in SQL Server?

To get only columns name you can use this query below: SELECT * FROM tempdb. sys. columns WHERE [object_id] = OBJECT_ID(N'tempdb..

How do I get all the field names of a table?

The following query will give the table's column names: SELECT column_name FROM INFORMATION_SCHEMA. COLUMNS. WHERE TABLE_NAME = 'News'

Can we use CTE in temp table?

You cannot create and drop the #TEMP table within the CTE query.


1 Answers

select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytemptable'); 
like image 76
kristof Avatar answered Oct 10 '22 16:10

kristof