Post reply

Drop temp tables

  • October 29, 2003 at 9:34 am

    #160338

    My stored proc produces a number of temp tables which may get quite large. Which are created may depend on the data. I need to drop them if they exist when they are no longer necessary. Having trouble with the

    IF EXISTS (SELECT * from what?

    Can someone post a reliable means of determining if the temp table exists in tempdb ?

    Thanks for your help

  • October 29, 2003 at 9:52 am

    #480143

    I would drop them at end of stored procedure using command "drop table #yourtemptablename".

    Edited by - allen_cui on 10/29/2003 09:52:33 AM

  • October 29, 2003 at 11:32 am

    #480161

    I made a real mole hill out of a mountain on this one. The tables are created by a select ... into #table so even if no rows are selected an empty table is created. I thought the empty table would not be created. Goes to show - need to do the experiment.

    Thank you

  • October 29, 2003 at 11:48 am

    #480164

    Your if exists could look like the following...

    

    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#temptable'))

        DROP TABLE #temptable

    

    

    or a simpler version...

    IF object_id('tempdb..#temptable') IS NOT NULL

        DROP TABLE #temptable

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • October 29, 2003 at 1:44 pm

    #480179

    I didn't think you had to explicitly drop temp tables created with the # prefix?

    I thought SQL Server cleaned these up for you.

  • October 29, 2003 at 2:56 pm

    #480190

    While SQL Server will drop them for you, I always clean up after myself when I can. It's just a good habit to do so. Also prevents errors if you run an sp multiple times without closing the window in QA.

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • October 29, 2003 at 3:44 pm

    #480204

    Also, one thing to point out is that you can cause blocking issues in tempdb by doing SELECT INTO ... #Tmp

    Better to explicitly create and drop the tables.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • October 30, 2003 at 6:12 am

    #480278

    Everything I've read has made me decide to always explicitly drop the temp tables at the end of my stored procedure in addition to checking if they exist at the beginning of my stored procedure and dropping them if they exist.

  • October 30, 2003 at 6:42 am

    #480282

    I always clean up after myself as well.

    Gary, Even if you don't drop the temp table within your stored proc you can run it multiple times within QA. When the proc completes any temp tables created in the proc will no longer exist.

  • October 30, 2003 at 12:44 pm

    #480352

    if object_ID('Tempdb..#Test') is not null drop table #Test

    Signature is NULL

  • October 30, 2003 at 12:47 pm

    #480355

    Oops...gljjr already said that. Never mind.

    Signature is NULL

  • October 31, 2003 at 2:24 pm

    #480520

    I agree about the cleanup - coding explicit behavior is a good practice to follow.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply