How to Find Source of Temp Tables

Question

In SSMS I see 7 temporary tables, all with names like dbo.#0876219E. When I end my connection and come back in, they are still there.

I'm not able to query them or get properties on them. I get errors.

How do I find their source, purpose and what they contain?

Is there a particular system sp that gives info on temp tables -- haven't been able to immediately find one.

If you restart the SQL service, are they still there?

user114600
– user114600

2010-11-22 22:53:35 +00:00
Commented Nov 22, 2010 at 22:53 — user114600
– user114600, Commented Nov 22, 2010 at 22:53

Damien_The_Unbeliever · Accepted Answer · 2010-11-24 07:52:15Z

2

Temp tables with such names (#, then 8 hex digits) are the temp tables that back table variables - declare @boris table (...)

They'll exist as long as the batch that introduced them continues to run. On my lightly loaded dev server, I can see ~30 of them, so I'm guessing there are some which are used/created by SQL Server itself - or possibly by SSMS.

answered Nov 24, 2010 at 7:52

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2010-11-24 05:23:41Z

0

Temp tables in SQL Server are perfectly normal. They are usually dropped when the database service is restarted (along with TempDB in which they are stored).

If you provide us with more context to where you see these temp tables, that would be helpful.

EDIT:

This quote is taken from the following forum post, which summarises the role of tempdb nicely:

tempdb is used for sorting, aggregation, distincts, unions, temp tables and table variables, row versioning. It can also be used for index building.

Oh and cursors

edited Nov 24, 2010 at 5:23

answered Nov 22, 2010 at 22:58

user114600

2 Comments

pghcpa Over a year ago

SSMS-Databases-System Databases-Tempdb-Tmporary Tables Lists seversal such as dbo.#0876219E, dbo.676A338E, etc. I can't tell what created these or what the contain; good to know they're normal.

user114600 Over a year ago

I've updated my answer with a nice summary of what tempdb is used for. There are many thousands of sources, including SQL Books Online, but I like this summary.

Collectives™ on Stack Overflow

How to Find Source of Temp Tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related