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

Nuts and bolts: Basics and Beyond

A table without details about its columns is just like a whisper in the wind. So, let's fetch more context than just names using join between tempdb.sys.columns and tempdb.sys.types:

-- It seems like SQL wanted to join the club too... pun intended 
SELECT c.name AS ColumnName, t.name AS DataType, c.max_length, c.is_nullable
FROM tempdb.sys.columns AS c
INNER JOIN tempdb.sys.types AS t ON c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID(N'tempdb..#YourTempTable');


With the above snippet, you'll end up with more than just column names. You'll also have details about the data types, nullability, and more!

Finding Dory or the object_id

To get the correct object_id of a temporary table, use:

-- Now we know where Nemo's dad went... he went inside SQL 
SELECT OBJECT_ID(N'tempdb..#YourTempTable');


Remember, if your temporary table starts with single hash (#), it's session-specific, so object_id will vary accordingly. double hash (#) indicates global visibility.

INFORMATION_SCHEMA.COLUMNS can be a safe friend when the going gets tough to write the deeply rooted schema-bound code.

Attention: Collisions ahead

In multi-user environments, remember the risk of session collisions, as distinct users may create temp tables with identical names leading to unexpected results. To avoid head-on collisions, appending the session-specific suffix to the table query is the seatbelt of your code!

Taking a ride on the stored procedures

For a more versatile tool, try SQL Server stored procedure sp_columns for fetching column details of a temporary table:

-- Knock Knock.. Who's there? SQL procedure!!! 
EXEC tempdb..sp_columns @table_name = N'#YourTempTable';


This command wraps up the tough logic and you won't need to join system tables or bother about catching the elusive object_id.

Furthering your exploration

If your playground gets bigger and complex, where dynamic SQL generation or creating utility procedures are the talk of the town, don't hesitate to write a T-SQL function that bundles up this logic preventing potential bugs and enhancing readability of your code.