Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: Validating the existance of a temp table

    Hi there,

    I was wondering if there's a way to check if a temp table already exists on my db.

    I used to do that for regular table:
    IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'myTableName')
    ... do something ....


    But it doesn't works with temp table... Is there a way to do it ?

    Thanks


    O
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I use MS SQL SERVER 2005 STD
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Dec 2002
    Posts
    50
    Temporary tables are stored in the tempdb database. So, you should be able to find it by searching the sysobjects table within that database.

    IF EXISTS(SELECT name FROM tempdb.'Username'.sysobjects WHERE type='U' AND name = 'myTableName')

    Where 'Username' is a valid username on the tempdb database.
    Last edited by Ishe; 10-16-07 at 18:06.

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    or, similarly (although this is SQL Server 2000)...
    Code:
    IF EXISTS (	SELECT * 
    		FROM tempdb.dbo.sysobjects
    		WHERE id = OBJECT_ID(N'[dbo].[#tempTable]')
    			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[#tempTable]
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Aug 2007
    Posts
    17
    SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name like '#temp%'

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    nicely done thank you all
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Orth, you know, temp tables are automatically deleted and the end of a procedure? You should not have to check for their existence...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ortho
    nicely done thank you all

    Really?

    Code:
    CREATE TABLE #tempTable(Col1 int)
    GO
    
    
    IF EXISTS (	SELECT * 
    		FROM tempdb.dbo.sysobjects
    		WHERE id = OBJECT_ID(N'[dbo].[#tempTable]')
    			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    PRINT 'True' ELSE Print 'False'
    GO
    
    DROP TABLE #tempTable
    GO
    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.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by blindman
    Orth, you know, temp tables are automatically deleted and the end of a procedure? You should not have to check for their existence...
    I was pretty sure they persisted for the duration of the session?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's for the length of the spid

    @@SPID
    Returns the server process identifier (ID) of the current user process.
    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.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm fairly certain that:

    temp tables exist for session
    table variables exist for the procedure

    EDIT: I stand corrected
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Teddy
    I was pretty sure they persisted for the duration of the session?
    Quote Originally Posted by georgev
    'm fairly certain that:

    temp tables exist for session
    table variables exist for the procedure

    EDIT: I stand corrected
    That's the point...

    And it can be useful not only to delete a table.
    But if the data exist you may not want to get it again.

    Anyway thanks for the answer
    Cheers
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ortho
    That's the point...

    And it can be useful not only to delete a table.
    But if the data exist you may not want to get it again.

    Anyway thanks for the answer
    Cheers
    I am blinded by the logic

    And if you tested my code you will find that it doesn't work
    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.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by georgev
    I'm fairly certain that:

    temp tables exist for session
    table variables exist for the procedure

    EDIT: I stand corrected
    A LOCAL temporary table created within a stored procedure is automatically deleted at the end of the procedure. It is not necessary to drop a temporary table at the conclusion of a procedure, nor is it necessary to check for its existence at the beginning of the procedure unless you are doing some really wacky sql.
    A GLOBAL temporary table follows different rules of scope, but global temps are rarely used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Brett Kaiser
    Really?

    Code:
    CREATE TABLE #tempTable(Col1 int)
    GO
    
    
    IF EXISTS (	SELECT * 
    		FROM tempdb.dbo.sysobjects
    		WHERE id = OBJECT_ID(N'[dbo].[#tempTable]')
    			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    PRINT 'True' ELSE Print 'False'
    GO
    
    DROP TABLE #tempTable
    GO
    OK, this irritates me *L* We have this check in many stored procedures. I guess I have some cleanup to do. I wondered why these stored procedures (inherited by me, of course, I would never do such a thing ) were checking for the temp table when it was a local one. I understand it if the table is a global one (which we also use on occasion, but I argue that such tables should just be "permanent" tables that are dropped later when we are done with them).

    I also see after some testing that it does not work for the global temp tables either.

    Thanks for the heads up, Brett! I guess we have some cleanup to do.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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