Where are my temporary tables?

[aharrisreid]

In the T-SQL window I have created some temporary tables using the syntax

SELECT * INTO #MyTempTable FROM MyTable.

Where can I find what temporary tables are currently available (I don't want to delete them yet using DROP TABLE ...)? I cannot find them in any enterprise mgr. table listing. Is it stored in memory like a VFP temporary cursor? What is the scope of these 'tables'? Are they present until the SQL session finishes?

Any help would be appreciated.

Alan

 

[jrbarnett]

Temporary tables go into the tempdb database. They are deleted when the connection that created it closed (a single # at the start indicates it was for a single connection, were it created with a double # at the start, it would stay until the last connection that used it was closed).

John

 

[aharrisreid]

jrbarnett, thanks for the reply.

>Temporary tables go into the tempdb database.

Can I view a listing of these tables? I've looked in the table-tree for the tempdb database and I cannot see the temporary tables.

Regards,
Alan

 

[jrbarnett]

They will have been delete automatically by SQL server when the connection gets closed. If you create it with Query Analyser, you can do a select * from tablename to see the contents.

To have the tables visible to a different connection, precede them with two hash symbols (eg ##MyTempTable). It will stay until all connections have closed, and will be visible to other applications in the SQL database.

John

 

[aharrisreid]

jrbarnett, thanks for the reply.

>They will have been delete automatically by SQL server when the connection gets closed. If you create it with Query Analyser, you can do a select * from tablename to see the contents.

Maybe I wasn't clear enough. When I said I want to view the tables I meant a list of temporary tables, not the actual contents of each table. I have since discovered I can do this by looking at the SysObjects list in the TempDB database.

Regards,
Alan