-
Dynamically Listing Created Temp Tables
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
-
Dynamically Listing Created Temp Tables (reply)
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|