Results 1 to 2 of 2

Thread: Dynamically Listing Created Temp Tables

  1. #1
    Andrew Guest

    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

  2. #2
    Juergen Leis Guest

    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
  •