Where To Find Temp Tables In SQL Server

Recently, I was working on a project where I was searching for a critical temp table. In this article, I will walk you through all the simple approaches to find temp tables in SQL Server.

Where To Find Temp Tables In SQL Server

Let us discuss all the approaches individually.

Approach-1: Using SQL Server Management Studio (SSMS)

To find temp tables in SQL Server, follow the below steps.

1. Launch SQL Server Management Studio (SSMS) and connect to your SQL server database instance.

2. Expand the Databases folder, Expand the System Databases folder, and then expand the tempdb folder; now expand the Tables folder, and then finally expand the Temporary Tables folder, as shown in the screenshot below. Now, you can see the available temp table.

Approach-2: Using SQL Query

We can query sys.objects to find the SQL server temp table details.

USE tempdb;
SELECT * FROM sys.objects 
WHERE name LIKE '#%'

After executing the above query, I got the list of available temp table details, as shown in the screenshot below.

Approach-3: Using sys.tables

We can also query the sys.tables to view temp tables in SQL Server.

SELECT *
FROM   tempdb.sys.tables

After executing the above query, I can see the temp tables in my SQL server, as shown in the screenshot below.

Video Tutorial

Conclusion

Now, you have the answer to the question where can I find temp tables in SQL server. You can either check it on the SystemDatabase -> tempdb -> Temporary Tables or you can query the sys.objects and tempdb.sys.tables as explained in this article.

You may also like following the articles below.

• How to Create Temp Table in SQL Server
• Temp table in stored procedure in SQL Server
• How To Backup A Table In SQL Server
• Find Store Procedure in SQL Server by Table Name