Temporary Table Column Name

Question

Temporary Table Column Name

Marie

SSChasing Mays

Points: 604
More actions
August 18, 2005 at 8:25 am

#87309

I there a way to get the column name from a temporary table?
SELECT COL_NAME(OBJECT_ID('#temptable'), 1)
does not work on temporary table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jcobble SSC Enthusiast Points: 181 More actions · Answer 1

Just curious why you would need to do this? You create the temp table through code, so you should know what the columns in it are.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Same question.. but I couldn't find a work around that didn't use the system tables directly :

create table #temp (name varchar(20) not null)

Select OBJECT_ID('tempdb.dbo.#temp')

Select name from tempdb.dbo.SysColumns where id = OBJECT_ID('tempdb.dbo.#temp') and Colid = 1

--name

DROP TABLE #temp

noeld SSC Guru Points: 96590 More actions · Answer 3

here is another way... ugly but you shouldn't need this anyways

USE tempdb

SELECT COL_NAME(OBJECT_ID('#temptable'), 1)

USE LocalDBName

* Noel

Marie SSChasing Mays Points: 604 More actions · Answer 4

You ask WHY would I want to know the column name of a table that was just created? To make a long story short, the limitations of report services is causing me to try some crazy crazy things!

I created a one record temp table with all of the field names as course names (i.e Math, Science etc.) When I find a student has passed a course I wanted to turn the Math flag on so that when this one record is sent to report services I can make the appropriate icon visible when ever the flag is on.

Anyway because I can't do this, (see below)

select @colname= 'Math' -- I am looping thru the student records and Math is a subject passed

update #temptable set @colname=1 where @colname = @colname

I am going to have to create a very very long case statement for now.

Thanks everyone for you help.

Marie

noeld SSC Guru Points: 96590 More actions · Answer 5

I am not an specialist in Reporting services but I believe you are approaching the problem the wrong way. You should be able to perform a simple select returning a list of the passed subjects and in the Report pivot that information.

* Noel

Marie SSChasing Mays Points: 604 More actions · Answer 6

I created a pivot/matrix in the report but I was not turn able to turn the image on or off based on the value in the matrix. It was a good suggestion even though it did not work. Any other suggestions???

Marie