One script to drop all temporary tables

  • I used code below to drop one temporary table. How to make code to drop all temporary tables?

    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL

    BEGIN

    DROP TABLE #Temp

    END

  • Repeat that code for each temporary table. Or just close the connection

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Why do you want a script to do this? Temp objects are automatically "managed" by the engine. Just close the connection and you don't have to worry about them. IIRC explicit drops aren't even honored any longer...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (1/16/2014)


    Why do you want a script to do this? Temp objects are automatically "managed" by the engine. Just close the connection and you don't have to worry about them. IIRC explicit drops aren't even honored any longer...

    Unless that changed for 2014, they are. The #temp drop functions for batch code. IE: Strip the query out of its procedure and put that on top so you can rerun while doing performance testing/data validation.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • TheSQLGuru (1/16/2014)


    IIRC explicit drops aren't even honored any longer...

    They are, they always have been. Drop a temp table and you can't access it any longer. Essential when testing code before putting into a procedure. 'cannot create #test because it already exists' is annoying.

    Now, the engine may choose to cache that temp table (strip out the metadata and just leave a couple of allocated pages), but it can do that with automatic drops on session close anyway.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Because I need test scripts in which there are a lot of temp tables. I need to modify codes or parameters to run more thank one time. If I do not delete temp tables, errors will occur.

  • adonetok (1/16/2014)


    Because I need test scripts in which there are a lot of temp tables. I need to modify codes or parameters to run more thank one time. If I do not delete temp tables, errors will occur.

    Unfortunately, there's no bulk code for it.

    Best thing you can do is just search the proc for CREATE TABLE # and then setup a bunch of if exists/drop at the top of the script.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • GilaMonster (1/16/2014)


    TheSQLGuru (1/16/2014)


    IIRC explicit drops aren't even honored any longer...

    They are, they always have been. Drop a temp table and you can't access it any longer. Essential when testing code before putting into a procedure. 'cannot create #test because it already exists' is annoying.

    Now, the engine may choose to cache that temp table (strip out the metadata and just leave a couple of allocated pages), but it can do that with automatic drops on session close anyway.

    The caching is what I meant - the object "stays around" in tempdb, although yes as you say you can't actually access it any longer.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I'm curious if there's any danger to doing something like this:

    DECLARE @SQL VARCHAR(MAX) =

    (

    SELECT 'DROP TABLE ' + STUFF(

    (

    SELECT ',' + name

    FROM tempdb.sys.tables

    FOR XML PATH('')

    ),1, 1, '')

    );

    PRINT @SQL;

    --EXEC sp_executesql @SQL;

    With the last statement uncommented of course.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Not enough time to check, but does tempdb.sys.tables show temp objects that aren't yours? Also, does it provide a name that is "droppable"?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (1/16/2014)


    Not enough time to check, but does tempdb.sys.tables show temp objects that aren't yours?

    Yes

    Also, does it provide a name that is "droppable"?

    No, though you could probably write code that gets it right most of the time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • TheSQLGuru (1/16/2014)


    GilaMonster (1/16/2014)


    TheSQLGuru (1/16/2014)


    IIRC explicit drops aren't even honored any longer...

    They are, they always have been. Drop a temp table and you can't access it any longer. Essential when testing code before putting into a procedure. 'cannot create #test because it already exists' is annoying.

    Now, the engine may choose to cache that temp table (strip out the metadata and just leave a couple of allocated pages), but it can do that with automatic drops on session close anyway.

    The caching is what I meant - the object "stays around" in tempdb, although yes as you say you can't actually access it any longer.

    The 'object' without it's name, without most of its metadata and without all but two (I think) pages. It's pretty much a drop, what SQL chooses to do behind the scenes is up to it, also it won't always be cached.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This works, unless you use #temp table names with more than two subsequent underscores or ending with an underscore:

    CREATE PROCEDURE [dbo].[dtt]

    AS

    -- drop all #temp tables for current session

    begin

    DECLARE @sql VARCHAR(60),

    @name VARCHAR(60)

    DECLARE dtt CURSOR

    FOR SELECT SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1)

    FROM tempdb.sys.tables AS t

    WHERE t.name LIKE '#%[_][_][_]%'

    AND t.[object_id] = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1))

    open dtt

    fetch next from dtt into @name

    while @@fetch_status <> -1

    BEGIN

    SELECT @sql = 'DROP TABLE ' + @name

    EXEC ( @sql )

    fetch next from dtt into @name

    END

    CLOSE dtt

    deallocate dtt

    END

    It works because OBJECT_ID returns NULL for #temp tables that belong to other sessions.

  • TheSQLGuru (1/16/2014)


    Not enough time to check, but does tempdb.sys.tables show temp objects that aren't yours? Also, does it provide a name that is "droppable"?

    Yes, the name that is in tempdb.sys.tables is "droppable" but yes it will also have all the additional characters like at the end of the table name "________________________________________________________________________________________________________________0000000E300B" at the end. Just in case you end up with some stupid naming conventions of temp tables in your script you can use the following just to be safe:

    DECLARE @sql NVARCHAR(MAX) = N''

    SELECT

    @sql = @sql + CHAR(10) + N'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME])

    FROM [tempdb].[INFORMATION_SCHEMA].[TABLES]

    PRINT @sql

    EXEC sp_executesql @sql

    Though you cannot recreate a table in the tempdb with the same name as a table created earlier in the same batch. For example the following code will cause an error if run in a single batch, but would execute normally if run in separate batches or even if the tables were not created in tempdb:

    SELECT

    1 AS [var]

    INTO #tab

    DROP TABLE [#tab]

    SELECT

    1 AS [var]

    INTO #tab

Viewing 14 posts - 1 through 13 (of 13 total)

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