How to retrieve field names from temporary table SQL Server 2008

Question

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

User · Answer

select    from tempdb INFORMATION SCHEMA COLUMNS  where TABLE NAME OBJECT NAME OBJECT ID   table

User · Answer

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

User · Answer

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

User · Answer

you can do it by following way too     create table  test  a int  b char 1    select   From  test  exec tempdb  sp columns   test

User · Answer

select   from tempdb sys columns where object id   object id  tempdb   mytemptable

User · Answer

select    from tempdb INFORMATION SCHEMA COLUMNS where table name like   MyTempTable

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to temp-tables