Dynamically Listing Created Temp Tables

You can get a list of all temporary tables by executing
SELECT * FROM tempdb..sysobjects WHERE type = 'U' AND name LIKE '#%'
and afterwards you can get a list of columns by using above id field
SELECT * FROM tempdb..syscolumns WHERE id = @id ORDER BY colid

The main problem is to identify which tables belong to which connection.
In SQL 7 and 2000 the problem can be solved by SELECTing @@CONNECIONS immediatly after logon (so you get the value of your connection number) and before any other user logs on.
This value (converted to hex and padded with leading zeros to 12 characters) can be found in SUBSTRING( name, 117, 12 ) of tempdb..sysobjects
Another way to get this CONNECTIONID would be, to create a uniquely named temp table and selecting its SUBSTRING( name, 117, 12 ) from tempdb..sysobjects

------------
Andrew at 1/21/2002 6:51:46 PM

Hi,

I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).

I need to know how to:

- Get a list of all temp tables created
- Get a list from each temp table of the columns created.

Hope someone can help!

Cheers,

Andrew