How to Find Source of Temp Tables

In SSMS I see 7 temporary tables, all with names like dbo.#0876219E. W开发者_开发知识库hen 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.

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.

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