Retrive column names from temp table

[SHAH MAULIK]

I am going to create dynamic temp table and I am looking for the columns available in that temp table for the same session id. you can refer code below for the same.

ALTER procedure abc
as
create table #tmp_table
(id int , name varchar(450) )

select c.* ,t.name from tempdb.sys.columns c
INNER JOIN tempdb.sys.tables t
on c.object_id = t.object_id
where t.name = '#tmp_table'
-- don't use like '%tmp_table%'

drop table #tmp_table
return 0

[CHill60]

From this article[^]

Quote:

Another oddity of the local temporary table (and the local temporary stored procedure) is that it has a different name in the metadata to the one you give it in your routine or batch. If the same routine is executed simultaneously by several processes, the Database Engine needs to be able to distinguish between the identically-named local temporary tables created by the different processes. It does this by adding a numeric string to each local temporary table name left-padded by underscore characters. Although you specify the short name such as #MyTempTable, what is actually stored in TempDB is made up of the table name specified in the CREATE TABLE statement and the suffix. Because of this suffix, local temporary table names must be 116 characters or less.

If you remove the where from your code you will see that the table name is actually

#tmp_table__________________________________________________________________________________________________________000000000002

So you do need to use a like clause but not the one you have commented out. Like this

where t.name LIKE '#tmp_table%'

[Richard Deeming]

That will return *all* copies of that temporary table for *all* sessions. Filtering by ``object_id`` would probably work better. :)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Richard Deeming]

Try filtering by ``object_id``: ``` select c.* ,t.name from tempdb.sys.columns c INNER JOIN tempdb.sys.tables t on c.object_id = t.object_id where t.object_id = OBJECT_ID('tempdb..#tmp_table') ```

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[CHill60]

Yep - good point :thumbsup:

[Mycroft Holmes]

I learn something most days, got my 5!

Never underestimate the power of human stupidity RAH

[Smart003]

instead of separate where condition use the condition in join statement only

select c.* ,t.name from tempdb.sys.columns c
INNER JOIN tempdb.sys.tables t
on (c.object_id = t.object_id
and t.name = '#tmp_table');

[CHill60]

Same problem will apply for the same reason I gave earlier - the table name does not equal '#tmp_table' so for this suggestion to work the ON clause will need to have

and t.name LIKE '#tmp_table**%**'

or

and t.object_id = OBJECT_ID('tempdb..#tmp_table')

(Credit to @Richard Deeming)

[SHAH MAULIK]

thanks .. It's working fine..