CODEWITHSUNDEEP
sqlsql-server-2005temp-tables
Shantanu Gupta
Shantanu Gupta

Reputation: 21188

Where Do Temporary Tables Get stored in sql server?

Where do temporary tables get stored in a database? I want to drop a temporary table if it already exists. I can do this for securable tables by querying at information schema but I don't know where temporary tables are stored.

Upvotes: 17

Views: 60168

Answers (5)

Nandha MV
Nandha MV

Reputation: 469

Here you can find all your temp tables (both local and global) which are stored and active using SSMS.

enter image description here

Upvotes: 0

sanjay
sanjay

Reputation: 41

TempDb Will In in SystemDatabase.Temp tables are stored here.

Upvotes: 4

Gurung
Gurung

Reputation: 163

Store at this table

SELECT *
FROM   tempdb.sys.tables

Delete query:

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql + ';', '') + 'drop table ' + QUOTENAME(NAME)
FROM   tempdb..sysobjects
WHERE  NAME LIKE '#%'

EXEC (@sql)

Upvotes: 3

Ankit Chaurasia
Ankit Chaurasia

Reputation: 81

Temporary tables gets stored in tempdb database which is present in SystemDatabase or SystemDatabase -> tempdb -> Temporary Tables

Upvotes: 8

Giorgi
Giorgi

Reputation: 30873

Temporary tables are stored in tempdb Database. There are various ways to check if a temp table exists outlined here: Check If Temporary Table Exists.

Upvotes: 22

Related Questions