Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: Verifing if exists a temporary table

    Hi,

    How can I verify if a temporary table called #X, for example, exists in database?

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    select * from tempdb..sysobjects

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Though the name will be appended with some random characters ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    And ther may be no guarantee that the temp table belongs to your connection.

    Best way I can think of knowing whether a temp table exists is by being absolutely sure when it is both created and dropped. In the case of a recursive procedure, you may want to add a nest level parameter. This will also help you from getting too deep into recursion.
    Last edited by MCrowley; 04-15-04 at 15:23.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Maybe there is some logic ...
    I created 4 temp tables from different sid


    #abcd_____________________________________________ __________________________________________________ ________________00000000002B
    #abcd_____________________________________________ __________________________________________________ ________________00000000002D
    #abcd_____________________________________________ __________________________________________________ ________________00000000002F
    #abcd_____________________________________________ __________________________________________________ ________________000000000031


    They are exactly 2 digits apart and exactly 128 characters
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Apr 2004
    Posts
    8
    Each instance of my application maintain a connection active and create a table temporary that store some informations. Triggers read these informations, but if a table don't exist a error is raised.
    Thus, in the trigger, I need verify if a table exist (to avoid the error).

    PS: Sorry my english.

    Thanks.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you having a trigger write to a temp table?

    I don't like the sound of this....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2004
    Posts
    8
    I need to store a integer value for each connection and these value is visible only in this connection (stored procedures and triggers), thus, my choice was to create a temp table for store these value.

    Originally posted by Brett Kaiser
    Why are you having a trigger write to a temp table?

    I don't like the sound of this....

  9. #9
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    to verify a temp table existence?

    if object_id ('tempdb.dbo.#x') is NOT NULL
    begin
    .........
    end



    Richard

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    He said he was reading from the temp table in the trigger, not writing to it. The last solution given to you before this post will work fine. The table will only be visible to the active connection, so you don't have to worry about multiple people having the "same name".
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    Apr 2004
    Posts
    8
    The solution work fine.

    Thanks.

    Originally posted by rding
    to verify a temp table existence?

    if object_id ('tempdb.dbo.#x') is NOT NULL
    begin
    .........
    end



    Richard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •