Where are temporary tables stored in SQL Server?

• Local temporary tables:
  • Only available to the current connection to the database for the current login
  • They are dropped when the connection is closed
• Global temporary tables:
  • Available to any connection upon their creation
  • They are dropped when the last connection using them is closed

Code Example – Local Temporary Table

CREATE TABLE #table_name ( 
column_name [DATATYPE]);
GO

Code Example – Global Temporary Table

CREATE TABLE ##table_name ( 
column_name [DATATYPE]);
GO

So, consider as an example that you have created the following temporary table:

CREATE TABLE #temp_table ( 
id INT, 
name VARCHAR(50) );
GO

--Query 1(a): Get the exact name of the temporary table you are looking for 
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] 
FROM tempdb..sysobjects 
WHERE name LIKE '#temp_table%');
GO

Explanation: When you declare a temporary table, SQL Sever adds some additional characters on its name in order to provide a unique system name for it and then it stores it in tempDB in the sysobjects table.

Even though you can query the temporary table with its logical name, internally, SQL Server knows it with the exact name. To this end, you need to execute the above query for finding the exact name of the temporary table.

--Query 1(b): Get column information for the temporary table 
-- by using the sp_columns stored procedure 
EXEC tempdb..sp_columns @table_name;
GO

Explanation: The sp_columnsstored procedure returns column information for the specified tables or views that can be queried in the current environment.