How To Find Temp Table In SQL Server

As a senior developer, I used to be required to work with temporary tables. But as you know, the temp table is crucial while working with an SQL server. So, I have identified a few approaches to this. In this article, I will walk you through all those approaches individually.

Approach-1: Using the tempdb System Views

To get the temp table in SQL Server, we can query the tempdb database. To this end, we can execute the SQL query below.

USE tempdb;
SELECT name, create_date, type_desc
FROM sys.objects
WHERE name LIKE '#%'
ORDER BY create_date DESC;

After executing the above query, I got the expected output below.

Check out How To Create A Temp Table In SQL Server

Approach-2: Using the sys.tables

We can also use the sys.tables to retrieve the temp table details using the below query.

SELECT name, create_date FROM TempDB.sys.tables WHERE name LIKE '#%'

After executing the above query, I got the expected output below.

You can also use the below query to get the orphaned temp tables.

USE tempdb;
SELECT name, create_date
FROM sys.objects
WHERE 
    name LIKE '#%' AND
    OBJECT_ID(name) IS NULL
ORDER BY create_date DESC;

After executing the above script, I got the expected output, as shown below.

Conclusion

Finding the temp tables in SQL Server is crucial for an SQL developer using the approaches mentioned in this article. As a developer, you choose the approach based on your requirements.

You may also like following the articles below.

• SQL Server If Object_ID Is Not Null Drop Table
• How To Insert Into Temp Table In SQL Server
• How To Drop Temp Table If Exists In SQL Server
• How to create index on temp table in SQL Server