How to Retrieve Field Names from Temporary Table (SQL Server 2008)

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

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');

Find the column names of Temp table

SELECT *
FROM   tempdb.sys.columns
WHERE  object_id = Object_id('tempdb..#sometemptable'); 

List down the column names in a Temporary Table in SQL Server

SELECT Obj.NAME
    ,Col.NAME
FROM tempdb.sys.objects Obj
INNER JOIN tempdb.sys.columns Col ON Obj.object_id = Col.object_id
WHERE Obj.NAME LIKE '#tmp%'

But please note that the local temporary table names will not be unique. We can have the same names from different sessions. So be careful with the query.

Getting columns from temporary @table

The problem is this line:

where o.name = object_id('tempdb..#tableName')

You are comparing a name to an object id. I'm not sure what you are really trying to do here, because you have a hard-coded name in the column. The table name has nothing to do with the temporary table, so this approach will not work.

Because table variables are handled differently from temporary tables, the system tables may not do what you want. Here is an answer on retrieving the names using XML.

Pass Column names for a Select Query to retrieve from temporary table

The following code does what you asked for:

Declare @temp table (ColumnNames varchar(30))

insert into @temp 
values('Id'), ('Name')

DECLARE @ColumnNames nvarchar(max)

SELECT @ColumnNames = stuff((SELECT ',' + ColumnNames 
FROM @temp
FOR XML PATH('')), 1,1, '')

EXEC (N'SELECT ' + @ColumnNames + N' FROM TheTable')

But something is fundamentally wrong with this. Why on Earth you need to store column names in a table variable and later read it to build the sql statement?

Get list of columns in a temp table in SQL Server

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

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

How to Sum All Columns from Temp Table in Microsoft SQL Server Like Value Counts in Pandas

I've compiled the below for you.

if exists(select name from Tempdb.Sys.Columns where Object_ID = Object_ID('tempdb..#My_Temp_Table'))
drop table #My_Temp_Table
go

if exists(select name from Tempdb.Sys.Columns where Object_ID = Object_ID('tempdb..#Temp_Table2'))
drop table #Temp_Table2
go

create table #My_Temp_Table
(   column1 float
,   column2 float
,   column3 float
,   column4 float
,   column5 float   )

insert into #My_Temp_Table
(   column1
,   column2
,   column3
,   column4
,   column5 )
values
    (1,0,3,5,3)
,   (2,2,0,5,5)
,   (0,7,0,3,0)

create table #Temp_Table2
(   [column] varchar(100) ,results  float   )

declare @loop int
,       @sqlCommand varchar(max)
,       @columnname varchar(100)
,       @result1    float   =   0
,       @result2    float   =   0

declare @ColumnTable table (id int identity primary key, columnname varchar(100))
insert into @ColumnTable (columnname)
select name
from Tempdb.Sys.Columns
where Object_ID = Object_ID('tempdb..#My_Temp_Table')

begin
    select @Loop = min(id) FROM @ColumnTable
    while @Loop IS NOT NULL
        begin
            select  @columnname =   (select columnname from @ColumnTable where id = @Loop)
            insert  #Temp_Table2 ([column]) select @columnname
            select @Loop = min(id) FROM @ColumnTable where id>@Loop
        end
end

set @sqlCommand = (select top 1  stuff((select distinct ',sum('+[column] + ') '+[column] from #Temp_Table2 for xml path('')), 1, 1, '') as lookup from #Temp_Table2)
set @sqlCommand = 'select '+@sqlCommand+' from #My_Temp_Table'
exec (@sqlCommand)

Would this work?

The result of this is in each column as requested and can be seen below:

---

Related Topics