Get list of columns in a temp table in SQL Server

sql sql-server tsql sql-server-2014
John Joseph picture John Joseph · Dec 22, 2016 · Viewed 8.3k times · Source

I am creating a temporary table on-the-fly using Select * Into #temp from SomeChangingSource in a stored procedure. I need to then list the resulting columns.

Handling this for a regular (permanent) table is as simple as:

select COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'

How do I handle this for a temporary table?

Answer

John Cappelletti picture John Cappelletti · Dec 22, 2016

Your were close. Just needed to point it to Tempdb.Sys.Columns

 Select * From  Tempdb.Sys.Columns Where Object_ID = Object_ID('tempdb..#TempTable')

Related questions

SQL Server after update trigger

I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table. CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE] …

Read More
Partition Function COUNT() OVER possible using DISTINCT

I'm trying to write the following in order to get a running total of distinct NumUsers, like so: NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth]) Management studio doesn't seem too happy about this. The error disappears when I remove …

Read More
Convert SQL Server Date to mm-yyyy

I'm trying to convert my Date which is (eg. 2012-04-20 05:54:59) format in into mm-yyyy. I came across some solutions that says you would need to convert into varchar . Is there any way using the Convert function ? Thanks :)

Read More