2

I am a beginner to SQL Server Database. I have a basic question.

How to retrieve the column names of a temporary table in SQL Server? I have tried querying the sys.objects, but the table is not listing there.

Krish
  • 166
  • 3
  • 13
  • 3
    The temporary table metadata will be stored in tempdb. – Gopakumar N.Kurup Jul 26 '18 at 16:23
  • Does this answer your question? [How to retrieve field names from temporary table (SQL Server 2008)](https://stackoverflow.com/questions/756080/how-to-retrieve-field-names-from-temporary-table-sql-server-2008) – casperOne May 12 '23 at 18:50

4 Answers4

2

Temp tables are stored in the tempdb database so you have to query the views from the tempdb database. Something like this:

SELECT c.name
FROM tempdb.sys.objects o
INNER JOIN tempdb.sys.columns c ON c.object_id = o.object_id
WHERE o.name LIKE '#TempTableName%';
squillman
  • 13,363
  • 3
  • 41
  • 60
1
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.

Gopakumar N.Kurup
  • 916
  • 4
  • 18
  • Thank you very much. Also thanks for sharing the information on different sessions as well. Is there a way to overcome this? – Krish Jul 26 '18 at 16:28
1

Here's how to get the temp table for just the current session.

SELECT c.name
FROM tempdb.sys.objects o
INNER JOIN tempdb.sys.columns c ON c.object_id = o.object_id
WHERE o.object_id = object_id('tempdb..#TempTableName)
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
1
`SELECT STUFF( 
   `(SELECT ',' + LTRIM(RTRIM(ISNULL(name,''))) 
      `FROM tempdb.sys.columns 
      `WHERE Object_ID = Object_ID(N'tempdb..#TEMP')
      `FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 1, '') 
`AS comma_separated_list
Wargames
  • 11
  • 3
  • 1
    Hello and welcome! You should use code blocks in questions and answers https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks –  Feb 21 '19 at 17:36