you might have used select X,Y,Z,* into #Foo from ....., and Once you have created the temporary table , you can then run this script to pull that table back out if you need to reuse it
Use tempdb;
GO
Declare @TableName varchar(100)= 'MyTempTable'
Declare @Output varchar(max)
Select @Output =
Coalesce(@Output, CHAR(13)) +
'[' + COLUMN_NAME + '] ' + c.DATA_TYPE + Case When Character_Maximum_Length is null then ''
else '('+Cast(Character_MAXIMUM_LENGTH as varchar)+')' end
+ Case When IS_NULLABLE = 'YES'
then ' NULL'
Else ' NOT NULL' end + ','
+ CHAR(13)
FROM Information_Schema.Columns c
Where TABLE_CATALOG ='Tempdb'
And TABLE_NAME like '#' +@Tablename +'%'
Order by c.ORDINAL_POSITION asc
Print 'Create Table #'+@TableName+' (' + Left(@Output,Len(@Output)-2)+CHAR(13)+')' 
Have you ever checked the size of the SQL Server tempdb after restarting SQL Server to find that it's reset? Simon Liew explains this behaviour.
2017-05-31
3,326 reads
Sergey Gigoyan looks at how to address the warning: Operator used tempdb to spill data during execution with spill level 1.
2016-01-22
3,958 reads
References and links to help you learn how to create multiple tempdb files.
2015-06-29
3,042 reads
With the introduction of SQL Server 2012 Microsoft officially supports local disk TempDB in SQL Server cluster configurations. This tip will show you how to configure TempDB on a local disk when installing your SQL Server 2012 cluster.
2012-12-20
2,993 reads
One of the most obvious bottlenecks in the performance of tempdb is caused by PAGELATCH, in-memory latch contention on the allocation bitmap of each data file used. We can use one of the rules-of-thumb to choose what should be roughly the best number of files, but how then do you check to see whether you've got it right for your data and workload?
2012-08-07
2,869 reads