Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


One script to drop all temporary tables


One script to drop all temporary tables

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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


J.Faehrmann
J.Faehrmann
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 1438
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.
bochambers
bochambers
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 82
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search